LeetCode Medium 1321 Amazon Point72 Interview SQL Question with Detailed Explanation

Question: leetcode.com/p...
In this video I solve and explain a medium difficulty leetcode SQL question using MySQL query. This question has been asked in Apple, Facebook, Amazon, Google, Adobe, Microsoft, Adobe interviews or what we popularly call FAANG interviews.
I explain the related concept as well. This question includes points to keep in mind to develop SQL queries.
LeetCode is the best platform to help you enhance your skills, expand your knowledge and prepare for technical interviews.
If you found this helpful, Like and Subscribe to the channel for more content.
#LeetCodeSQL #FAANG #SQLinterviewQuestions

[@yosupalex8276]

Thanks for sharing! I was stuck on how to filter out the first 6 days without changing moving total and avg. Your CTE solution saved my life!

[@EverydayDataScience]

Glad that the video was helpful. Happy querying! 😊

[@yiqunwang4025]

When dates are not consecutive, the sum over rows between 6 preceding and current row becomes invalid and returns wrong answer.

[@prajwalkhairnar7006]

In problem statement itself it is mentioned that (there will be at least one customer every day), so issue won't occur

[@kulyashdahiya2529]

Thank you so much

[@AI-ew1rj]

Can you explain why the aggregation in the CTE is necessary? The question asks for a moving average across all customers - I understood this to be an average even if different customers visited on the same day.
How do we know that the moving average needs to be unique to visited on?

[@santoshrai4866]

Acc. to my inderstanding you simply add 6 in the query but what if they visited on date = 30 then date become 36 ? can you explain where i'm wrong

[@tarunleekha7375]

Cant we add in row_num by visited_on in cte2 and then filter it with row_num greater than 6

[@stellawon8546]

just curious for the second temp table, is the sum over a window function? But it seems to miss the partition by part..

[@gunjanpatil8191]

Nice video. One suggestion from my side- can you make solution videos on hard questions ??

[@EverydayDataScience]

I started with the Hard questions as well. #102

[@aparnakesarkar4648]

A suggestion, if you can write the name of the problem, in this case "Restaurant Growth" in your video title, it'll be easier to search

[@EverydayDataScience]

That’s a great suggestion, Aparna. Sure, from next videos, I’m gonna mention name of the problem as well.

[@pritamm6386]

we added 6 here ...on what .... Limit or on visited_on date?
we can directly add number 6 on visited date?

[@mohitpandey39]

Running same code it is showing correct but on submission it showing wrong why

[@suba_sah]

Your solution does not pass all the test cases

[@rx1268]

I used the same code, but when I submitted it, it didn't pass all the test cases. Does anybody know why was that?

[@milansingh8288]

Instead of ROW use RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW

[@sahithim9278]

Use this query. It works for sure.
WITH cte AS
(SELECT visited_on, SUM(amount) AS total_amount
FROM Customer
GROUP BY visited_on),
cte2 AS
(SELECT
a.visited_on,
SUM(b.total_amount) AS amount,
ROUND(SUM(b.total_amount)/7,2) AS average_amount
FROM cte a, cte b
WHERE DATEDIFF(a.visited_on, b.visited_on) BETWEEN 0 AND 6
GROUP BY a.visited_on
ORDER BY a.visited_on)
SELECT *
FROM cte2
WHERE visited_on >= (SELECT visited_on
FROM cte
ORDER by visited_on
LIMIT 1 ) + 6
ORDER BY visited_on

[@aryakaagnihotri3210]

@@sahithim9278 it worked , thanks a lot

[@rupdeepthey]

Great!

[@sauravchandra10]

The ORDER BY in the last line of query was unnecessary.

[@mohdzuhaib-r1m]

select visited_on,amount,average_amount from (select visited_on, sum(sum(amount)) over (order by visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) as amount,
round(avg(sum(amount)) over (order by visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ),2) as average_amount,
count(visited_on) over (order by visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) as day_count
from customer group by visited_on )z
where day_count=7

[@sachindewangan6378]

This one was quiet trickey

[@EverydayDataScience]

Glad that you found the video useful, Sachin.

[@prajjawalpandit2106]

#Updated Solution
# Write your MySQL query statement below
WITH cte AS
(SELECT visited_on, SUM(amount) AS total_amount
FROM Customer
GROUP BY visited_on),
cte2 AS
(SELECT
a.visited_on,
SUM(b.total_amount) AS amount,
ROUND(SUM(b.total_amount)/7,2) AS average_amount
FROM cte a, cte b
WHERE DATEDIFF(a.visited_on, b.visited_on) BETWEEN 0 AND 6
GROUP BY a.visited_on
ORDER BY a.visited_on)
SELECT *
FROM cte2
WHERE visited_on >= (SELECT visited_on FROM cte ORDER by visited_on LIMIT 1 ) + 6
ORDER BY visited_on

[@dhirucrafts]

why b.total_amount works , a.total_amount does not works

[@tanmaythaker2905]

All test cases passed.
SELECT visited_on, amount, ROUND(amount/7, 2) average_amount
FROM (
SELECT DISTINCT visited_on,
SUM(amount) OVER(ORDER BY visited_on RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) amount,
MIN(visited_on) OVER() 1st_date
FROM Customer
) t
WHERE visited_on>= 1st_date+6;

[@ujjwalvarshney3188]

lect t.visited_on, r1 as amount ,round(r2,2) as average_amount from
(select X.* , sum(X.amount) over (order by X.visited_on rows between 6 preceding and current row ) as r1 , avg(X.amount) over (order by X.visited_on rows between 6 preceding and current row ) as r2 from
(select visited_on ,sum(amount) as amount from Customer
group by 1 order by visited_on ) as x
order by x.visited_on) as t where t.visited_on >= (select min(visited_on)
+ 6 from Customer )