[Day 16] solvesql | Advent of SQL 2024 | 스테디셀러 작가 찾기🔥

스테디셀러 작가 찾기

✔️0212 0822 복습

 

문제 조건

1. 5년 이상 연속으로 베스트셀러 작품 목록에 이름을 올린 소설 작가와 연도 정보를 출력

2. 이때 서로 다른 작품이더라도 상관없음

3. 단, 같은 해에 여러 작품으로 베스트셀러 목록에 들어가더라도 1번으로 카운트 

 

Solution

WITH T1 AS (
   SELECT DISTINCT author
        , year
   FROM books
   WHERE genre = 'Fiction'
  ), T2 AS (
  SELECT author,
         year,
         LAG(year, 4) OVER (PARTITION BY author ORDER BY year) as lag_4
  FROM T1
  )
  SELECT author,
         MAX(year) as year,
         COUNT(*) + 4 as depth
  FROM T2 
  WHERE lag_4 IS NOT NULL AND (year - lag_4 = 4)
  GROUP BY author
  
  /*
  WITH steady_seller AS (
  SELECT *
       , LAG(year, 4) OVER (PARTITION BY author ORDER BY year) AS lag_4 -- 최소 5년 연속 올랐다 치면, 무조건 "year-lag_4=4" 성립되어야 함
  FROM (
    SELECT DISTINCT author
         , year
    FROM books
    WHERE genre = 'Fiction'
    ) AS preprocessed
)
SELECT author
     , MAX(year) AS year
     , MAX(year) - MIN(lag_4) + 1 AS depth
    --  , COUNT(*) + 4 AS depth_1
FROM steady_seller
WHERE lag_4 IS NOT NULL
  AND year - lag_4 = 4
GROUP BY author
*/

 

▶ T1 CTE:

소설 장르의 책만 필터링하고 중복된 (작가, 연도) 쌍을 제거한 테이블을 반환한다. 

 

▶ T2 CTE:

5년 이상 연속으로 베스트셀러 작품 목록에 올랐는지 판단하기 위해 LAG 함수를 활용한다.

동일 작가의 베스트셀러 달성 연도 중, 현재 행에서 이전 4번째 행의 연도를 반환한다.

 

(예)

 

▶ 최종 SELECT:

(year - lag_4 = 4): 현재 연도(year)와 4번째 이전 연도(lag_4)가 정확히 4년 간격이어야 5년 이상 연속 조건을 만족한다. 

 

(예)

 

 

피드백

 

LAG 함수

LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY order_column)

 

column_name:
값을 참조할 열(컬럼)을 지정한다.

offset (선택 사항):
참조할 행의 상대적 위치를 지정한다.
기본값은 1로, 바로 이전 행의 값을 반환한다.

 

default_value (선택 사항):
참조하려는 행이 없을 경우 반환할 기본값을 지정한다.
기본값을 지정하지 않으면 NULL 반환 

 

OVER (PARTITION BY ... ORDER BY ...):
    PARTITION BY: 데이터를 그룹화하여 각각 독립적으로 처리한다. 생략하면 전체 데이터셋을 하나로 간주
    ORDER BY: 참조할 이전 행의 순서를 지정