SQL Interview Question - Solution (Part - IX) |

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

[@AbhijitPaldeveloper]

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]

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

[@VARUNTEJA73]

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]

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]

@@MeanLifeStudies ok tq sir