SQL Interview Question - Solution (Part - IX) | Data Analyst | Data Engineer | SQL

Подписаться 674

50% 1

#education #sql #dataanalyst #dataengineers #dataanalytics #inteview #sqlinterview #dataanalystinterview #dataengineerinterview #interviewpreparation #datascience
#MeanLifeStudies
Here are my medium and Github links mentioned below. You will get more information regarding data analyst or data engineer interviews and portfolio project building. These pages will definitely help you in your preparation. Don't forget to catch these pages using the below links.
/ mahendraee204
github.com/mahendra204
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);

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

22 июн 2024

Ссылка:

Скачать:

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

Добавить в:

Мой плейлист

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

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

@VARUNTEJA73 12 дней назад

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 12 дней назад

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 12 дней назад

@@MeanLifeStudies ok tq sir

@AbhijitPaldeveloper 6 дней назад

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 6 дней назад

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