구름 아카이브

저작권 문제로 인해, 직접 작성한 쿼리문만 공개.

16. 스테디셀러 작가 찾기

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

WITH Fiction_author AS (
    SELECT DISTINCT
      author,
      year
    FROM books
    WHERE genre = "Fiction"
    ORDER BY author
)

, Sequence_num AS (
    SELECT
      author,
      year,
      year - ROW_NUMBER() OVER(PARTITION BY author ORDER BY year) AS group_split
    FROM Fiction_author
)

SELECT
  author,
  MAX(year) AS year,
  COUNT(*) AS depth
FROM Sequence_num
GROUP BY author, group_split
HAVING COUNT(*) >= 5