[LeetCode] 1321. Restaurant Growth

기존 코드도 좋지만, window function의 연산 범위를 잘 익혀야 한다!

 

Question

 

You are the restaurant owner and you want to analyze a possible expansion (there will be at least one customer every day).

Compute the moving average of how much the customer paid in a seven days window (i.e., current day + 6 days before). average_amount should be rounded to two decimal places.

Return the result table ordered by visited_on in ascending order.

The result format is in the following example.

 

Code (Window Function)

 

select distinct visited_on
        , sum(amount) over(order by visited_on range between interval 6 day preceding and current row) as amount
        , round( sum(amount) over(order by visited_on range between interval 6 day preceding and current row)/7 ,2) as average_amount

from customer
order by 1
limit 10000 offset 6

 

Code (My Code)

 

select distinct c.visited_on
        , (select sum(amount)
                from customer
                where visited_on between c.visited_on - interval 6 day and c.visited_on) as amount
        , (select round(sum(amount)/7,2) 
                from customer
                where visited_on between c.visited_on - interval 6 day and c.visited_on) as average_amount

from customer c
where visited_on >= (select min(visited_on) + interval 6 day from customer)

order by 1

 

My Code