LeetCode Medium 1321 Amazon Point72 Interview SQL Question with Detailed Explanation

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

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

[@tarunleekha7375]

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

[@kulyashdahiya2529]

Thank you so much

[@stellawon8546]

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

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

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

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

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

[@mohitpandey39]

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

[@rupdeepthey]

Great!

[@pritamm6386]

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

[@suba_sah]

Your solution does not pass all the test cases

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

[@sachindewangan6378]

This one was quiet trickey

[@EverydayDataScience]

Glad that you found the video useful, Sachin.

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

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

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