SQL Interview Question - Solution (Part - IX) |

[@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