[SQL] Restaurant Growth(LeetCode/Oracle)

안녕하세요!

 

이번 포스팅은 LeetCode에 있는 Restaurant Growth 문제를 OracleDB로 풀어보려고 합니다!

 

(모든 문제는 Oracle로 풀이하겠습니다.)

 

1. 문제 링크 : https://leetcode.com/problems/restaurant-growth/description/

 

2. 문제

Column name	Type
customer_id	int
name	varchar
visited_on	date
amount	int

[문제] 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.

7일 기간(즉, 현재 + 6일 전)에 고객이 결제한 금액의 이동 평균을 조회하는 문제입니다.

 

3. 제출 쿼리 및 설명

 

1) 날짜 별 AMOUNT의 총합을 구합니다.

SELECT VISITED_ON, SUM(AMOUNT) AS AMOUNT
FROM CUSTOMER
GROUP BY VISITED_ON

2) 1번 쿼리를 인라인 뷰로 활용해서 현재 + 6일 전까지의 금액의 이동 평균을 조회합니다. 

1월 7일부터 결과가 나와야 하기 때문에 첫날(FIRST_DATE)를 추가합니다.

SELECT
  VISITED_ON, MIN(VISITED_ON) OVER() AS FIRST_DATE,
  AMOUNT, SUM(AMOUNT) OVER(ORDER BY VISITED_ON
                         RANGE INTERVAL '6' DAY PRECEDING) AS TOTAL_AMOUNT
FROM (
  SELECT VISITED_ON, SUM(AMOUNT) AS AMOUNT
    FROM CUSTOMER
  GROUP BY VISITED_ON
)

3) VISITED_ON >= FIRST_DATE + 6 조건을 추가해서 1월 1일부터 6일까지의 결과는 제외하고 7일부터 결과가 나오게 합니다.

SELECT VISITED_ON, TOTAL_AMOUNT
FROM ( 
    SELECT
      VISITED_ON, MIN(VISITED_ON) OVER() AS FIRST_DATE,
      AMOUNT, SUM(AMOUNT) OVER(ORDER BY VISITED_ON
                             RANGE INTERVAL '6' DAY PRECEDING) AS TOTAL_AMOUNT
    FROM (
      SELECT VISITED_ON, SUM(AMOUNT) AS AMOUNT
        FROM CUSTOMER
      GROUP BY VISITED_ON
    )
)
WHERE VISITED_ON >= FIRST_DATE + 6

최종) 최종 결과는 누적합에서 7을 나눠 이동평균을 조회합니다.

SELECT 
  TO_CHAR(VISITED_ON, 'YYYY-MM-DD') AS VISITED_ON,
  TOTAL_AMOUNT AS AMOUNT,
  ROUND(TOTAL_AMOUNT / 7, 2) AS AVERAGE_AMOUNT
FROM (
  SELECT VISITED_ON, TOTAL_AMOUNT
  FROM ( 
    SELECT
      VISITED_ON, MIN(VISITED_ON) OVER() AS FIRST_DATE,
      AMOUNT, SUM(AMOUNT) OVER(ORDER BY VISITED_ON
                             RANGE INTERVAL '6' DAY PRECEDING) AS TOTAL_AMOUNT
    FROM (
      SELECT VISITED_ON, SUM(AMOUNT) AS AMOUNT
        FROM CUSTOMER
      GROUP BY VISITED_ON
    )
  )
  WHERE VISITED_ON >= FIRST_DATE + 6
)
ORDER BY VISITED_ON ASC
;