문제

advent S16 스테디셀러 작가 찾기
(자세한 문제는 생략)

풀이 과정

데이터/문제 이해

books 테이블은 아마존 쇼핑몰의 2009~2019년 판매량 상위 50개의 책 정보가 담긴 테이블이다

이 중에서 5년 이상 연속으로 베스트셀러 목록에 올라간 작가를 모아 소설 스테디셀러 작가 특별전을 열고자 한다.
5년 이상 연속으로 베스트셀러 작품 목록에 이름을 올린 작가와 연도 정보를 출력하라

여기서 하나 주의할 점.
스테디셀러 소설 작가 특별전
Americal Psychological Association이 10년 연속 베스트 셀러여도, 제외해야 한다는 것
genre = 'Fiction'만 고려해야 한다

전처리

소설 작가만을 찾기 위해 where절을 설정하고
문제에서 필요한 author, year 외의 컬럼은 모두 포함하지 않는다

SELECT
  author
  , year
FROM
  books
WHERE
  genre = 'Fiction'
ORDER BY
  author
  , year
;

연속된 row를 하나의 그룹으로

연속된 연도를 구하는 방법은, index(=row_number)와 year의 차를 구하는 방법이 있다
author별로 row_number()를 부여하고, year와 row_number의 차이를 구하면, 동일한 연도끼리는 같은 값을 갖게 된다
단, 해당 문제의 경우 한 작가가 같은 해에 여러 베스트셀러를 낼 수도 있으므로, 먼저 서브쿼리를 통해 distinct를 적용했다

SELECT
  *
  , row_number() over(partition by author order by year) as "index"
  , year - row_number() over(partition by author order by year) as "diff"
FROM (
  SELECT
    DISTINCT author
    , year
  FROM
    books
  WHERE
    genre = 'Fiction') aa
ORDER BY
  author
  , year
;

group by 작가, diff

group by를 수행할 때, 당연하겠지만 year는 조건으로 포함하면 안 된다
그러면 그냥 거의 모든 row를 그대로 분리해서 출력하는 것이니까
우리가 원하는 것은 작가별로, 연속한 연도 묶음마다 1개의 row만 출력하는 것이다
이를 구분하기 위해 앞서 diff 컬럼을 만들었으니, author와 diff를 통해 group by를 실행한다
동시에 having절을 통해 5년 이상으로 연속된 연도일 경우만 남기도록 하면 답이 도출된다

WITH selling_year_index AS (
  SELECT
    *
    , row_number() over(partition by author order by year) as "index"
    , year - row_number() over(partition by author order by year) as "diff"
  FROM (
    SELECT
      DISTINCT author
      , year
    FROM
      books
  WHERE
    genre = 'Fiction') aa
  ORDER BY
    author
    , year
)
SELECT
  author
  , max(year) as "year"
  , count(*) as "depth"
FROM
  selling_year_index
GROUP BY
  author
  , diff
HAVING 
  count(*) >= 5
;

번외: 베스트 셀러를 물처럼 마시는 작가 중복 제거

해당 데이터는 2009~2019년으로 10년짜리 데이터이므로,
5년 연속 베스트셀러 - n년간 안식년 - 5년 연속 베스트셀러라는 케이스가 존재할 수 없다.
다만 현실에선 혹시라도 이런 경우가 있을 수 있다는 점을 고려해, 쿼리를 좀 더 디벨롭해볼 수 있다.

물론 방식은 간단하다
그냥 row_number() 하나로 끝

WITH selling_year_index AS (
  SELECT
    *
    , row_number() over(partition by author order by year) as "index"
    , year - row_number() over(partition by author order by year) as "diff"
  FROM (
    SELECT
      DISTINCT author
      , year
    FROM
      books
  WHERE
    genre = 'Fiction') aa
  ORDER BY
    author
    , year
),
best_sellers AS (
  SELECT
    author
    , max(year) as "year"
    , count(*) as "depth"
  FROM
    selling_year_index
  GROUP BY
    author
    , diff
  HAVING 
    count(*) >= 5
)
SELECT
  author
  , year
  , depth
FROM (
  SELECT
    *
    , row_number() over (partition by author order by year desc) as "rn"
  FROM
    best_sellers
) b
WHERE
  rn = 1
;