[LeetCode] 1321. Restaurant Growth

 

 

[문제]

이미지를 누르면 문제 링크로 넘어갑니다

 

 

[MySQL]

WITH daily AS (
        SELECT visited_on
            , SUM(amount) AS daily_amount
        FROM Customer
        GROUP BY visited_on)
, moving_amount AS (
        SELECT visited_on
            , LAG(visited_on, 6) OVER (ORDER BY visited_on) AS first_date
            , SUM(daily_amount) OVER (ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS amount
            , ROUND(AVG(daily_amount) OVER (ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS average_amount
        FROM daily)

SELECT visited_on
     , amount
     , average_amount
FROM moving_amount
WHERE first_date IS NOT NULL