SQL Interview Question - Solution (Part

No video

SQL Interview Question - Solution (Part - IX) |

Рет қаралды 1,202

Күн бұрын

#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);

Пікірлер: 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