[리트코드] 1321. Restaurant Growth - ROWS BETWEEN 6 PRECEDING AND CURRENT ROW✔️✔️✔️

 

# 문제

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

 

# 문제 요약

 

7일 이상부터 7일동안의 amount합계와 평균값 구하기 (소수점2자리)

 

 

# 핵심 개념

 

1. 먼저 group by visited_on으로 같은 날짜의 amount값을 합쳐주었다

-> 가상테이블 cte1생성

 

2. 집계함수 over ( order by / rows between 6 preceding and current row) as 별칭으로

7일동안의 합과 갯수를 세어주었다

-> 가상테이블 cte2 생성

sum(amount) over (order by visited_on
rows between 6 preceding and current row) as amount,

count(*) over (order by visited_on
rows between 6 preceding and current row) as num

 

 

여기까지 코드

with cte1 as (
    select visited_on,
           sum(amount) as amount
    from Customer
    group by visited_on
), cte2 as (
    select visited_on,
       sum(amount) over (order by visited_on
       rows between 6 preceding and current row) as amount,
       count(*) over (order by visited_on
       rows between 6 preceding and current row) as num
    from cte1
    group by visited_on
    order by visited_on
)
select *
from cte2

 

 

[ 결과 ]

 

 

따라서 해당 값에서 num이 7이상인 것만 가져와서

amount, round(amount/7, 2)를 해주었다

 

 

# 정답 코드

 

with cte1 as (
    select visited_on,
           sum(amount) as amount
    from Customer
    group by visited_on
), cte2 as (
    select visited_on,
       sum(amount) over (order by visited_on
       rows between 6 preceding and current row) as amount,
       count(*) over (order by visited_on
       rows between 6 preceding and current row) as num
    from cte1
    group by visited_on
    order by visited_on
)
select visited_on,
       amount,
       round(amount/7, 2) as average_amount
from cte2
where num >= 7
order by visited_on

 

 

 

# 결과