SQL | SOVESQL 2024 🎄: Day16. 스테디셀러 작가 찾기

Day16. 스테디셀러 작가 찾기

https://solvesql.com/problems/find-steadyseller-writers/

with t1 as (
  select author, year
  from books
  where genre = 'Fiction'
  group by author, year
  order by author, year
), t2 as (
  select author
    , year as p1
    , lead(year,4) over(partition by author order by year asc) as p5
  from t1
)

select author, max(p1) as year , count(DISTINCT p1) as depth
from t2
where author in (select DISTINCT author
                  from t2
                  where p5 is not null
                    and p1+4 = p5)
group by author

❗WHERE 절에 서브쿼리로 조건 놓기
❗서브쿼리 : P5가 비어있지 않고 연속인 것)

 

 

 

+) WITH 절 : T1

❗WHERE 절에 장르 조건 걸기
❗GROUP BY 로 작가와 년도 지정하기

 

 

 

+) WITH 절 : T2

❗LEAD() 와 윈도우 함수 사용해서 4년 뒤의 것 칼럼 지정하기
-> 5년 이상 베스트 셀러인 것 표기