[SQL문제풀기] 스테디셀러 작가 찾기

silver

Mar 29, 2025

Contents

문제 SQLite

문제

solvesql.com

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

SQLite

내가 작성한 오답

: 소.설.작가를 찾는 문제인데 장르를 생각을 하지 않고 5년이상 베스트셀러 작가를 찾았다.


with a as (select author, year,
                year - row_number() over(partition by author order by year) num
          from (select distinct author, year from books))
select author, max(year) year, count(*) depth
from a 
group by author, num
having count(num) >= 5

내가 작성한 정답1 - row_number()


with a as (select author, year,
                year - row_number() over(partition by author order by year) num
          from (select distinct author, year from books where genre = 'Fiction'))
select author, max(year) year, count(*) depth
from a 
group by author, num
having count(num) >= 5

내가 작성한 오답2 - lag()

: 정답처리 되긴 하지만 연속된 여러 구간이 있다면 구간 별로 나누는게 아니라 다 더해지기 때문에 오답이다.


with a as (select author, year,
                    lag(year) over(partition by author order by year) py
            from (select distinct author,year from books where genre='Fiction')),
b as (select author, year,
              case when year - py = 1 or py is null then 1
              else 0 end d
        from a)
select author, max(year) year, count(d) depth
from b
group by author,d
having count(d) >= 5

내가 작성한 정답2 - lag()

: 작가 별 연도를 정렬한 후 연속되지 않은 해에서 증가하는 번호로 그룹을 나눴다


with a as (select author, year,
                    lag(year) over(partition by author order by year) py
            from (select distinct author,year from books where genre='Fiction')),
b as (select author, year,
            sum(d) over (partition by author order by year rows unbounded preceding) gr
      from (select author, year,
                    case when year - py = 1 or py is null then 0
                    else 1 end d
              from a))
select author, max(year) year, count(year) depth
from b 
group by author, gr
having count(year) >= 5