1321. Restaurant Growth / LeetCode, SQL, MS SQL Server

문제

source: LeetCode

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

Write an SQL query to 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 result table ordered by visited_on in ascending order.

 

 

제출답안(MS SQL Server)

WITH
    daily_sales
        AS (SELECT visited_on, 
                SUM(amount) AS daily_sales
            FROM 
                customer
            GROUP BY 
                visited_on),
    cumulative_sales
        AS (SELECT 
                visited_on,
                daily_sales, 
                SUM(daily_sales) OVER(ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_total
            FROM daily_sales)


SELECT
    visited_on, 
    moving_total AS amount, 
    ROUND(1.0*moving_total/7, 2) AS average_amount

FROM
    cumulative_sales
WHERE
    visited_on >= (SELECT DATEADD(DAY, 6, MIN(visited_on)) FROM customer)

 

 

 

풀이(MS SQL Server)

CTE1: 날짜별 총 판매

WITH
    daily_sales
        AS (SELECT visited_on, 
                SUM(amount) AS daily_sales
            FROM 
                customer
            GROUP BY 
                visited_on),

 

CTE2: 날짜별 7일 판매 누적

    cumulative_sales
        AS (SELECT 
                visited_on,
                daily_sales, 
                SUM(daily_sales) OVER(ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_total
            FROM daily_sales)

>> ROWS BETWEEN 6 PRECIDING AND CURRENT ROW를 사용할 경우, 이전 행이 6개보다 적은 경우 NULL이 아니라 적은대로 산출함 (아래 참고)

>> 따라서, 메인 쿼리에 과거행이 6개가 안되는 데이터는 제외하도록 해야함

메인쿼리

SELECT
    visited_on, 
    moving_total AS amount, 
    ROUND(1.0*moving_total/7, 2) AS average_amount

FROM
    cumulative_sales
WHERE
    visited_on >= (SELECT DATEADD(DAY, 6, MIN(visited_on)) FROM customer)

>> moving_total의 경우 데이터 타입이 INT로 되어있기 때문에 평균도 INT 기준으로 구해지면서 오차가 발생함. 따라서 1.0을 곱함으로써 데이터 타입을 FLOAT로 캐스트 하였음

>> 이전 행의 갯수가 6개가 안되는 데이터는 WHERE 구절을 통해 걸러냄

 

 

 

 

문제 바로가기(MS SQL Server)