SQL문제풀기

[SQL 문제 풀이] Restaurant Growth (레스토랑 성장)

Jun 25, 2025

[SQL 문제 풀이] Restaurant Growth (레스토랑 성장)

Contents

내가 작성한 쿼리

Restaurant Growth - LeetCode

Can you solve this real interview question? Restaurant Growth - Table: Customer +---------------+---------+ | Column Name | Type | +---------------+---------+ | customer_id | int | | name | varchar | | visited_on | date | | amount | int | +---------------+---------+ In SQL,(customer_id, visited_on) is the primary key for this table. This table contains data about customer transactions in a restaurant. visited_on is the date on which the customer with ID (customer_id) has visited the restaurant. amount is the total paid by a customer. 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. Example 1: Input: Customer table: +-------------+--------------+--------------+-------------+ | customer_id | name | visited_on | amount | +-------------+--------------+--------------+-------------+ | 1 | Jhon | 2019-01-01 | 100 | | 2 | Daniel | 2019-01-02 | 110 | | 3 | Jade | 2019-01-03 | 120 | | 4 | Khaled | 2019-01-04 | 130 | | 5 | Winston | 2019-01-05 | 110 | | 6 | Elvis | 2019-01-06 | 140 | | 7 | Anna | 2019-01-07 | 150 | | 8 | Maria | 2019-01-08 | 80 | | 9 | Jaze | 2019-01-09 | 110 | | 1 | Jhon | 2019-01-10 | 130 | | 3 | Jade | 2019-01-10 | 150 | +-------------+--------------+--------------+-------------+ Output: +--------------+--------------+----------------+ | visited_on | amount | average_amount | +--------------+--------------+----------------+ | 2019-01-07 | 860 | 122.86 | | 2019-01-08 | 840 | 120 | | 2019-01-09 | 840 | 120 | | 2019-01-10 | 1000 | 142.86 | +--------------+--------------+----------------+ Explanation: 1st moving average from 2019-01-01 to 2019-01-07 has an average_amount of (100 + 110 + 120 + 130 + 110 + 140 + 150)/7 = 122.86 2nd moving average from 2019-01-02 to 2019-01-08 has an average_amount of (110 + 120 + 130 + 110 + 140 + 150 + 80)/7 = 120 3rd moving average from 2019-01-03 to 2019-01-09 has an average_amount of (120 + 130 + 110 + 140 + 150 + 80 + 110)/7 = 120 4th moving average from 2019-01-04 to 2019-01-10 has an average_amount of (130 + 110 + 140 + 150 + 80 + 110 + 130 + 150)/7 = 142.86

Restaurant Growth - LeetCode

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

Restaurant Growth - LeetCode

notion image


귀하는 레스토랑 주인이며 가능한 확장을 분석하고자 합니다(매일 최소 한 명의 고객이 있을 것입니다).

고객이 7일 동안 지불한 금액의 이동 평균을 계산합니다(즉, 현재 날짜 + 6일 전).
average_amount은 소수점 두 자리로 반올림해야 합니다.

visited_on이 정렬한 결과 테이블을 오름차순으로 반환합니다.

내가 작성한 쿼리

MySQL


SELECT 
    c1.visited_on, -- 기준 날짜 (현재 날짜)
    SUM(amount) AS amount, -- 기준 날짜 포함 이전 6일간 거래 금액 합계
    ROUND(SUM(amount) / 7, 2) AS average_amount -- 7일간 평균 금액 (소수점 둘째 자리까지 반올림)
FROM (
		SELECT DISTINCT visited_on FROM Customer
) c1  -- 방문한 날짜만 중복 없이 뽑은 서브쿼리 (기준 날짜 목록)
JOIN Customer c2
    ON DATEDIFF(c1.visited_on, c2.visited_on) BETWEEN 0 AND 6  -- c2의 방문일이 기준 날짜(c1)로부터 최대 6일 전 ~ 당일 사이인 경우
GROUP BY c1.visited_on -- 기준 날짜별로 그룹핑
HAVING COUNT(DISTINCT c2.visited_on) = 7 -- 7일 연속 데이터(당일 포함 7일)가 모두 존재하는 날짜만 결과에 포함
ORDER BY c1.visited_on; -- 날짜 오름차순 정렬

Oracle


SELECT 
    c1.visited_on,
    SUM(amount) AS amount,
    ROUND(SUM(amount) / 7, 2) AS average_amount
FROM (
    SELECT DISTINCT visited_on FROM Customer
) c1
JOIN Customer c2
	  ON (c1.visited_on - c2.visited_on) BETWEEN 0 AND 6
GROUP BY c1.visited_on
HAVING COUNT(DISTINCT c2.visited_on) = 7
ORDER BY c1.visited_on;

차이점

구분	MySQL	Oracle
날짜 차이 계산	`DATEDIFF(date1, date2)` 사용	`date1 - date2`로 직접 뺄셈

Share article

stupefyee

RSS·Powered by Inblog