LeetCode Medium 1321 Amazon Point72 Interview SQL Question with Detailed Explanation

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

[@suba_sah]

Your solution does not pass all the test cases

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

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

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

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

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

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

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

[@sauravchandra10]

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

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

[@stellawon8546]

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

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

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

[@rupdeepthey]

Great!

[@sachindewangan6378]

This one was quiet trickey

[@EverydayDataScience]

Glad that you found the video useful, Sachin.

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