문제

advent S13 게임 개발사의 주력 플랫폼 찾기
(자세한 문제는 생략)

풀이 과정

필요 컬럼 추출

해당 문제에서 사용되는 테이블은 platforms, companies, games로 3개이다
사실 platforms, companies는 마지막에 id를 받아서 이름만 출력해주면 되기에 일단 차치해두고,
games 하나만 사용하면 된다

문제에서 요구하는 바는 '개발사 별로', '플랫폼 별', '판매량의 최대'이기에, 이에 해당하는 컬럼을 먼저 추출하면 된다
'판매량 최대'는 sales 관련 컬럼 4개를 더해주면 된다

아래 코드의 경우 platform_id, developer_id로 group by를 묶어줄 것이기 때문에 한 번에 sum()까지 사용했다

SELECT
  platform_id
  , developer_id
  , sum(sales_eu + sales_jp + sales_na + sales_other) as "tot_sales"
FROM
  games
WHERE
  (platform_id is not NULL)
  and (developer_id is not null)
GROUP BY
  platform_id
  , developer_id
;

rank 계산

문제에서 요구하는 바를 보면
'매출량 합계가 동일한 경우 모두 출력'하라고 되어있다
이를 통해 row_number와 rank(혹은 dense_rank) 중에서 어떤 함수를 사용해야 하는지가 결정되었다
동일한 수치를 부여하여 조건을 적어야 하므로, 후자인 rank() 함수를 사용해야 한다

WITH preprocess AS (
  SELECT
    developer_id
    , platform_id
    , sum(sales_eu + sales_jp + sales_na + sales_other) as "tot_sales"
  FROM
    games
  WHERE
    (platform_id is not NULL)
    and (developer_id is not null)
  GROUP BY
    platform_id
    , developer_id
  order BY
    developer_id
    , tot_sales desc
)
SELECT
  *
  , rank() over (partition by developer_id order by tot_sales desc) as "rnk"
FROM
  preprocess
;

판매량 top1만 남기기

이제는 쉽다.
rnk = 1인 값들만 남기면,
그게 개발사 별, 플랫폼 별 매출량 합계 1위인 경우이니까.
WHERE절을 작성하고, 이왕 코드 끝나는 거 아까 치워두었던 platforms와 companies 테이블을 가져와 id를 name으로 바꿔주면 끝난다.

최종 코드

WITH preprocess AS (
  SELECT
    developer_id
    , platform_id
    , sum(sales_eu + sales_jp + sales_na + sales_other) as "tot_sales"
  FROM
    games
  WHERE
    (platform_id is not NULL)
    and (developer_id is not null)
  GROUP BY
    platform_id
    , developer_id
  order BY
    developer_id
    , tot_sales desc
)
, ranking AS (
  SELECT
    *
    , rank() over (partition by developer_id order by tot_sales desc) as "rnk"
  FROM
    preprocess
)
SELECT 
  c.name as "developer"
  , p.name as "platform"
  , r.tot_sales as "sales"
FROM
  ranking r
  INNER JOIN platforms p
    ON r.platform_id = p.platform_id
  INNER JOIN companies c
    ON r.developer_id = c.company_id
WHERE
  r.rnk = 1
;

주요 포인트

이 문제의 핵심은 2가지인데,
(1) 개발사 별, 플랫폼 별 매출량 합계를 구하는 과정
(2) 최대 매출량 케이스를 구할 때 rank와 row_number 중 선택하는 것
이다.

전자의 경우, sum()을 사용하지 않으면 group by 결과 그냥 상위에 있던 값만 남게 되므로 결과가 전혀 달라지게 된다.
후자의 경우에도, row_number()를 사용하면 중복되는 값이 있을 때 상위에 있던 값 1개만 남게 된다.