[Leetcode] Restaurant Growth

문제 링크

정답

WITH SumByDate AS (
    SELECT 
        visited_on,
        SUM(amount) daily_amount
    FROM Customer
    GROUP BY visited_on
)
SELECT
    visited_on,
    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 SumByDate
ORDER BY 1
LIMIT 1000 OFFSET 6;

✅ Point

• 총 7일간 (current day + 6 days before) 의 amount의 총합, 평균 구하기
• 7일씩 (7개의 연속적인 row) 로 SUM, AVG를 구하는 것이 핵심
  • OVER를 활용하면 구할 수 있음
  • OVER: 앞에 선언한 WINDOW 함수가 적용될 row 집합의 범위 및 순서를 결정
• OVER + ROWS
  • OVER ROWS BETWEEN (범위) AND (범위) 형태로 선언
    • (범위) = (정확한 범위) + (기준)
      • (정확한 범위): 숫자 or UNBOUNDED
      • (기준): PRECEDING / CURRENT ROW / FOLLOWING