SQL 코드카타 116 - Restaurant Growth

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

 

Column Name	Type
customer_id	int
name	varchar
visited_on	date
amount	int

 

Customer 테이블:

• customer_id  : 테이블의 고유 키
• name : 고객의 이름
• visited_on : 고객이 레스토랑을 방문한 날짜
• amount : 고객이 지불한 총 금액

당신은 레스토랑 주인으로서 확장을 고려하고 있으며, (적어도 매일 한 명 이상의 고객이 있을 것입니다).

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

결과 테이블은 visited_on을 오름차순으로 정렬하여 반환하세요.

 

 

select
    visited_on,
    sum(amount) sum_amount
from Customer
group by visited_on

 

먼저 날짜별로 누적합을 구하기 위해서 날짜별 합계를 구해준다.

 

select
    visited_on,
    SUM(sum_amount) OVER (ORDER BY visited_on) Total_amount
from
(select
    visited_on,
    sum(amount) sum_amount
from Customer
group by visited_on) a

 

위의 쿼리를 서브쿼리로 바꿔준 뒤, 날짜를 기준으로 누적합을 계산한다.

 

select
    visited_on,
    SUM(sum_amount) OVER (ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) Total_amount
from
(select
    visited_on,
    sum(amount) sum_amount
from Customer
group by visited_on) a

 

누적합계의 조건을 설정한다.

ROWS : 현재 행 기준으로

BETWEEN A AND B : A와 B사이의 기간

6 PRECEDING : 이전 6개의 행부터

CURRENT ROW : 현재 행 까지

 

select *
from
(select
    visited_on,
    SUM(sum_amount) OVER (ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) amount
from
(select
    visited_on,
    sum(amount) sum_amount
from Customer
group by visited_on) a) a
where 
    visited_on >= (SELECT MIN(visited_on) FROM Customer) + INTERVAL 6 DAY

 

다시 한번 서브쿼리로 묶어준 뒤, 최근 6개의 값( 7일 이전의 값)들들 제외한다.

 

select
    visited_on,
    amount,
    round(amount/7, 2) average_amount 
from
(select
    visited_on,
    SUM(sum_amount) OVER (ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) amount
from
(select
    visited_on,
    sum(amount) sum_amount
from Customer
group by visited_on) a) a
where 
    visited_on >= (SELECT MIN(visited_on) FROM Customer) + INTERVAL 6 DAY

 

평균을 구해서 추가해주면 완료!

성공!