[SQL_Q] 1321. Restaurant Growth

https://leetcode.com/problems/restaurant-growth/description/

문제

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.
 

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.

 

Example 1:

Input: 
Customer table:
+-------------+--------------+--------------+-------------+
| customer_id | name         | visited_on   | amount      |
+-------------+--------------+--------------+-------------+
| 1           | Jhon         | 2019-01-01   | 100         |
| 2           | Daniel       | 2019-01-02   | 110         |
| 3           | Jade         | 2019-01-03   | 120         |
| 4           | Khaled       | 2019-01-04   | 130         |
| 5           | Winston      | 2019-01-05   | 110         | 
| 6           | Elvis        | 2019-01-06   | 140         | 
| 7           | Anna         | 2019-01-07   | 150         |
| 8           | Maria        | 2019-01-08   | 80          |
| 9           | Jaze         | 2019-01-09   | 110         | 
| 1           | Jhon         | 2019-01-10   | 130         | 
| 3           | Jade         | 2019-01-10   | 150         | 
+-------------+--------------+--------------+-------------+
Output: 
+--------------+--------------+----------------+
| visited_on   | amount       | average_amount |
+--------------+--------------+----------------+
| 2019-01-07   | 860          | 122.86         |
| 2019-01-08   | 840          | 120            |
| 2019-01-09   | 840          | 120            |
| 2019-01-10   | 1000         | 142.86         |
+--------------+--------------+----------------+
Explanation: 
1st moving average from 2019-01-01 to 2019-01-07 has an average_amount of (100 + 110 + 120 + 130 + 110 + 140 + 150)/7 = 122.86
2nd moving average from 2019-01-02 to 2019-01-08 has an average_amount of (110 + 120 + 130 + 110 + 140 + 150 + 80)/7 = 120
3rd moving average from 2019-01-03 to 2019-01-09 has an average_amount of (120 + 130 + 110 + 140 + 150 + 80 + 110)/7 = 120
4th moving average from 2019-01-04 to 2019-01-10 has an average_amount of (130 + 110 + 140 + 150 + 80 + 110 + 130 + 150)/7 = 142.86

내 풀이

with daily_paid as (
    SELECT visited_on, sum(amount) daily_amount
    FROM Customer
    group by visited_on
), 
mv_avg as (
	SELECT visited_on, 
    sum(daily_amount) over (
        order by visited_on
        rows between 6 PRECEDING AND CURRENT ROW
    ) as amount,
    avg(daily_amount) over (
        order by visited_on
        rows between 6 PRECEDING AND CURRENT ROW
    ) as average_amount,
    ROW_NUMBER() OVER (ORDER BY visited_on) AS idx
    FROM daily_paid
)
select visited_on, 
amount, 
round(average_amount,2) as average_amount
FROM mv_avg
where idx >=7

이번 쿼리는 성능이 준수하게 나와서 다른 사람들의 쿼리와 비교하는 파트는 없지만 오늘 배운 내용을 정리하는 시간을 가져보자.

moving average 를 구할 때 자주 사용하는 표현인데,
암기하면 매우 편리하다.

avg(daily_amount) over (
        order by visited_on
        rows between 6 PRECEDING AND CURRENT ROW
    ) as average_amount

n PRECEDING : 이전 n 행
CURRENT ROW : 현재 행

기본문법

<윈도우 함수> OVER (
  PARTITION BY <그룹기준컬럼>    -- 그룹 나누기 (선택적)
  ORDER BY <정렬기준컬럼>        -- 정렬 기준 (필수)
  ROWS BETWEEN <시작> AND <끝>   -- 물리적 행 기준 범위 (선택적)
)

윈도우 함수 관련 추가적인 내용은 윈도우 함수에서 ROWS, RANGE, PARTITION BY의 차이 에 정리해 둔 게 있으니 까먹지 말고 복습 ㄱㄱ