LeetCode Medium 1321 Amazon Point72 Interview SQL Question with Detailed Explanation

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

[yosupalex]

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!

[AI-ewrj]

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?

[yiqunwang]

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

[stellawon]

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

[tarunleekha]

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

[mohitpandey]

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

[santoshrai]

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

[rx]

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

[pritamm]

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

[aparnakesarkar]

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

[gunjanpatil]

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

[sauravchandra]

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

[suba_sah]

Your solution does not pass all the test cases

[mohdzuhaib-rm]

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

[prajjawalpandit]

#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

[ujjwalvarshney]

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 )

[tanmaythaker]

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;