[SolveSQL/Advent of SQL 2024] 스테디셀러 작가 찾기

문제

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

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

 

 

이전행의 결과를 가져오는 윈도우함수 LAG()만 알고있었으면 금방 풀었을텐데 이 함수를 몰라서 좀 헤맸다.

 

 

1. 작가, 달성연도, LAG (달성연도) as prevYear 로 정렬

2. 달성연도 - prevYear이 1인값만 SUM하면 간단하게 끝

 

WITH CTE as (
SELECT 
author, 
year,
LAG(Year) OVER ( PARTITION BY Author ORDER BY Year) as prevYear
FROM books
WHERE genre = 'Fiction'
), result as (
SELECT 
  author, 
  MAX(Year) as year,
  SUM ( CASE WHEN Year - prevYear = 1 THEN 1 ELSE 0 END ) + 1 as depth
FROM CTE
GROUP BY Author
)
SELECT 
author, 
year, 
depth
FROM result
WHERE depth >= 5