[leetcode] 1321. Restaurant Growth/7일간의 이동 평균

1321. Restaurant Growth

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

Restaurant Growth - 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.

 

# 당신은 식당 주인이고 가능한 확장을 분석하려고 합니다(매일 최소 한 명의 고객이 있을 것입니다).
# 고객이 7일 창(즉, 현재 날짜 + 6일 전)에 지불한 금액의 이동 평균을 계산하는 SQL 쿼리를 작성합니다. 

 

정답 코드

1. WITH문

# 1
WITH cus_table AS -- 방문일별 총 금액
    (SELECT visited_on, SUM(amount) AS amount
     FROM Customer
     GROUP BY visited_on)

SELECT c1.visited_on,
    ROUND(SUM(c2.amount),2) as amount,
    ROUND(AVG(c2.amount),2) as average_amount
FROM cus_table c1 JOIN cus_table c2 
    ON DATEDIFF(c1.visited_on, c2.visited_on ) BETWEEN 0 AND 6
GROUP BY 1
HAVING count(distinct c2.visited_on) = 7
ORDER BY 1

 

2. 서브쿼리

# 2
SELECT a.visited_on AS visited_on, SUM(b.day_sum) AS amount,
       ROUND(AVG(b.day_sum), 2) AS average_amount
FROM
  (SELECT visited_on, SUM(amount) AS day_sum FROM Customer GROUP BY visited_on ) a,
  (SELECT visited_on, SUM(amount) AS day_sum FROM Customer GROUP BY visited_on ) b
WHERE DATEDIFF(a.visited_on, b.visited_on) BETWEEN 0 AND 6
GROUP BY a.visited_on
HAVING COUNT(b.visited_on) = 7