SQL | SOVESQL 2024 🎄: Day 13. 게임 개발사의 주력 플랫폼 찾기

Day 13. 게임 개발사의 주력 플랫폼 찾기

https://solvesql.com/problems/main-platform-of-game-developers/

WITH T1 AS (
  SELECT c.name AS developer
    , p.name AS platform
    , SUM(g.sales_eu+g.sales_jp+g.sales_na+g.sales_other) AS sales
  FROM games AS g
    INNER JOIN platforms AS p ON g.platform_id = p.platform_id 
    INNER JOIN companies AS c ON g.developer_id = c.company_id
  GROUP BY c.name, p.name
  ORDER BY c.name, p.name
), T2 AS (
  SELECT * 
    , RANK() OVER(PARTITION BY developer ORDER BY sales DESC) AS RNK
  FROM T1
)

SELECT developer, platform, sales
FROM T2
WHERE RNK = 1

❗WHERE 절에 순위가 1인 것만 보게 필터링

 

 

 

+) WITH 절 : T1

❗게임사의 매출을 하나의 매출로 합침
❗INNER JOIN 으로 테이블 결합
❗GROUP BY로 회사이름, 플랫폼 이름으로 그룹화

 

 

 

+) WITH 절 : T2

❗RANK() 윈도우 함수 사용해서  개발자, 매출 내림차순을 기준으로 순위 매기기