[SQL] solvesql Advent of SQL 2024 16일차

# 링크

• solvesql Advent of SQL 2024: https://solvesql.com/collections/advent-of-sql-2024/

https://solvesql.com/collections/advent-of-sql-2024/

• 16일차 - 스테디셀러 작가 찾기: https://solvesql.com/problems/find-steadyseller-writers/

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

 

 

# 풀이

WITH fictions AS (
  SELECT DISTINCT
    author,
    year
  FROM books
  WHERE genre = 'Fiction'
),
consecutive_years AS (
  SELECT 
      author, 
      year, 
      year - ROW_NUMBER() OVER (PARTITION BY author ORDER BY year) AS group_id
  FROM fictions
),
grouped_authors AS (
  SELECT 
      author, 
      COUNT(*) AS depth,
      MIN(year) AS start_year,
      MAX(year) AS end_year
  FROM consecutive_years
  GROUP BY author, group_id
)
SELECT 
    author, 
    end_year AS year, 
    depth
FROM grouped_authors
WHERE depth >= 5;

• fictions CTE
  • SELECT DISTINCT: 중복된 데이터를 제거한다.
    
    • author: author 컬럼을 선택한다.
    • year: year 컬럼을 선택한다.
  • FROM books: books 테이블에서 데이터를 가져온다.
  • WHERE genre = 'Fiction': genre가 'Fiction'인 데이터만 선택한다.
• consecutive_years CTE
  • SELECT
    
    • author: author 컬럼을 선택한다.
    • year: year 컬럼을 선택한다.
    • year - ROW_NUMBER() OVER (PARTITION BY author ORDER BY year) AS group_id: author별로 year을 기준으로 정렬한 후 번호를 매기고, year에서 해당 번호를 뺀 값을 group_id로 저장한다.
  • FROM fictions: fictions CTE에서 데이터를 가져온다.
• grouped_authors CTE
  
  • SELECT
    
    • author: author 컬럼을 선택한다.
    • COUNT(*) AS depth: 데이터의 개수를 depth로 저장한다.
    • MIN(year) AS start_year: year의 최솟값을 start_year로 저장한다.
    • MAX(year) AS end_year: year의 최댓값을 end_year로 저장한다.
  • FROM consecutive_years: consecutive_years CTE에서 데이터를 가져온다.
  • GROUP BY author, group_id: author과 group_id를 기준으로 그룹화한다.
• SELECT
  
  • author: author 컬럼을 선택한다.
  • end_year AS year: end_year 컬럼을 year로 저장한다.
  • depth: depth 컬럼을 선택한다.
• FROM grouped_authors: grouped_authors CTE에서 데이터를 가져온다.
• WHERE depth >= 5: depth가 5 이상인 데이터만 선택한다.