SQL 문제 풀이 챌린지(13/25) - 게임 개발사의 주력 플랫폼 찾기

저작권 문제로 인해, 직접 작성한 쿼리문만 공개.

 

 

 

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

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

WITH main_platform AS (
  SELECT
    games.developer_id,
    games.platform_id,
    SUM(games.sales_na + games.sales_eu + games.sales_jp + games.sales_other) AS sales,
    
    DENSE_RANK() OVER (PARTITION BY games.developer_id ORDER BY SUM(games.sales_na + games.sales_eu + games.sales_jp + games.sales_other) DESC
    ) AS denserank
  FROM games
  GROUP BY games.developer_id, games.platform_id
)

SELECT
  companies.name AS developer,
  platforms.name AS platform,
  sales 
FROM main_platform

    INNER JOIN companies ON main_platform.developer_id = companies.company_id
    INNER JOIN platforms ON main_platform.platform_id = platforms.platform_id
WHERE main_platform.denserank = 1
ORDER BY companies.name, platforms.name;