스테디셀러 작가 찾기 - SolveSQL

스테디셀러 작가 찾기

출처 : 스테디셀러 작가 찾기 SolveSQL

내 답안📕

WITH new_table AS (
  SELECT author
       , year
  FROM books
  WHERE genre = 'Fiction'
  GROUP BY author, year
)

SELECT author
     , MAX(year) AS year
     , COUNT(*) AS depth
FROM (
  SELECT *
      , ROW_NUMBER() OVER (PARTITION BY author ORDER BY year ASC) AS rnk
      , year - ROW_NUMBER() OVER (PARTITION BY author ORDER BY year ASC) AS diff
  FROM new_table
) AS t
GROUP BY author, diff
  HAVING depth >= 5;

나는 분명 맞게 푼거 같은데..

계속 답이 틀렸다고 해서.. 해석을보니

'소설' 작가들만 필터링을 했었어야했다..

다음부터는 문제를 더 잘읽도록 하자!