1321. Restaurant Growth

Tables

erDiagram
	Customer {
		customer_id int PK
		name varchar
		visited_on date PK
		amount int
	}
Mermaid
복사

Question

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.

The result format is in the following example.

DataFlow

customer -> group by visited_on -> daily_paid a customer -> group by visited_on -> daily_paid b a -> left join b -> on datadiff a.visited_on , b.visited_on -> group by a.visited_on -> row_number, sum, avg -> b1 b1 -> where row_number >= 7

날짜 단위로 sum한 두개의 테이블을 datediff 조건으로 left join하여 집계 테이블 생성
datediff(a, b)는 a - b로  -> a.visited_on에 일주일씩 집계 데이터를 붙이게 된다.
row_number 사용 7일 이후 부터 데이터 출력

이 방법은 subquery 사용하여 해결 가능한 방법이고 window function을 사용한 풀이도 가능

Answer

SELECT
	visited_on,
	amount,
	average_amount
FROM (
	SELECT
		a.visited_on,
		row_number() OVER(ORDER BY a.visited_on) AS rn,
		SUM(b.amount) AS amount,
		ROUND(AVG(b.amount), 2) AS average_amount
	FROM (
		SELECT
			visited_on,
			SUM(amount) AS amount
		FROM Customer
		GROUP BY visited_on
		) a
	LEFT JOIN (
		SELECT
			visited_on,
			SUM(amount) AS amount
		FROM Customer
		GROUP BY visited_on
		) b
		ON DATEDIFF(a.visited_on, b.visited_on) BETWEEN 0 AND 6
	GROUP BY a.visited_on
) b1
WHERE rn >= 7
ORDER BY visited_on ASC;
SQL
복사

SELECT
	visited_on,
	sum(amount) OVER(ORDER BY visited_on ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS amount,
	ROUND(AVG(amount) OVER(ORDER BY visited_on ROWS BETWEEN 7 PRECEDING AND CURRENT ROW), 2) AS average_amount,
	ROW_NUMBER() OVER(ORDER BY visited_on ASC) AS rn
FROM customer
ORDER BY visited_on ASC;
SQL
복사

window function으로 해결하는 것보다 subquery와 left join으로 해결하는 방법이 정확안 값을 출력한다. window function은 행의 개수가 충족되지않아도 현제 행을 기준으로 값을 출력한다.