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

문제

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