SQL Interview Question - Solution (Part - IX) |

Подписаться 1,2 тыс.

Просмотров 1,3 тыс.

50% 1

#education #sql #dataanalyst #dataengineers #dataanalytics #inteview #sqlinterview #dataanalystinterview #dataengineerinterview #interviewpreparation #datascience
#meanlifestudies
Here are My profiles that will definitely help your preparation for data analyst or data engineer roles.
Medium: / mahendraee204
Github: github.com/mah...
Here are create and insert statements:
-------------------------------------------------------------
create table shopping (
store_id varchar(20),
location varchar(20),
customer_id int,
date date,
amount int
);
insert into shopping values
('S1', 'Hyderabad', 100, '2024-06-10', 56000),
('S1', 'Bangalore', 101, '2024-06-11', 15800),
('S1', 'Chennai', 102, '2024-06-13', 12000),
('S1', 'Hyderabad', 102, '2024-06-14', 18000),
('S2', 'Hyderabad', 101, '2024-06-11', 80000),
('S2', 'Bangalore', 101, '2024-06-12', 25000),
('S2', 'Bangalore', 100, '2024-06-15', 10000),
('S3', 'Chennai', 102, '2024-06-12', 9000),
('S3', 'Hyderabad', 100, '2024-06-09', 66000);

Опубликовано:

7 сен 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 5

@AbhijitPaldeveloper 2 месяца назад

Question: Which customer has visited the same store location twice for consecutive 2 days and next date shopping amount is higher than previous day In mysql SELECT customer_id, amount FROM(SELECT *, lag(date) OVER(PARTITION BY customer_id, location ORDER BY date ASC) as pre_date, lag(amount) OVER(PARTITION BY customer_id, location ORDER BY date ASC) as pre_amount FROM `shopping`) as e WHERE amount>pre_amount AND DATE_ADD(date, INTERVAL -1 DAY) = pre_date;

@MeanLifeStudies 2 месяца назад

Not same store. A location has two different stores. So same location.

@VARUNTEJA73 2 месяца назад

Q. Which customer has visited to store twice and get amount less one compared to another one A. select customer_id,amount from( select store_id,customer_id,amount,count(customer_id)as counts, row_number()over(partition by store_id,customer_id order by amount desc)ranks from shopping group by store_id,customer_id,amount)t1 where t1.ranks=2 order by customer_id Is this right sir ?

@MeanLifeStudies 2 месяца назад

Yes, you are correct theoretically and you extracted the same expected output. But technically speaking all stores are not in the same location. so we should not take them individually in the partition. because in different locations we have different stores. Ex: Hyd: S1,S2, S3,..... and Bang: S1,S2,S3,...... and Chen: S1,S2,S3,... If All stores are in the same location then your answer is perfect. I hope you understand. Well done. You almost solved it.

@VARUNTEJA73 2 месяца назад

@@MeanLifeStudies ok tq sir