Restaurant Growth - LeetCode

Restaurant Growth 문제

출처 : LeetCode Restaurant Growth

Table
Customer

Column Name	Type
customer_id	int
name	varchar
visited_on	date
amount	int

In SQL,(customer_id, visited_on) is the primary key for this table.
This table contains data about customer transactions in a restaurant.
visited_on is the date on which the customer with ID (customer_id) has visited the restaurant.
amount is the total paid by a customer.

Q.

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.

---

질문

레스토랑 매출 데이터를 날짜 기준으로 보면서, 각 날짜마다 최근 7일의 평균 매출을 계산하는 문제!

즉, 2019-01-09면 2019-01-03 ~ 2019-01-09까지의 평균 매출을 구하라는 의미

내 답안 📕

WITH daily_sale AS (
    SELECT visited_on
         , SUM(amount) AS amount
    FROM Customer
    GROUP BY visited_on
), t AS (
    SELECT visited_on
         , SUM(amount) OVER(ORDER BY visited_on ASC RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) AS amount
         , AVG(amount) OVER(ORDER BY visited_on ASC RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) AS average_amount
         , ROW_NUMBER() OVER() AS rnk
    FROM daily_sale
)

SELECT visited_on
     , amount
     , ROUND(average_amount, 2) AS average_amount
FROM t
WHERE rnk >= 7
ORDER BY visited_on ASC;