SQL 문제 풀이 챌린지(17/25) - 멀티 플랫폼 게임 찾기

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

 

 

 

17. 멀티 플랫폼 게임 찾기
https://solvesql.com/problems/multiplatform-games/

WITH major_platform_game AS (

    SELECT
      games.name AS game_name,
      games.platform_id,
      games.publisher_id,
      companies.company_id,
      companies.name AS company_name,
      platforms.name AS platform_name,
      CASE 
          WHEN platforms.name IN ('PS3', 'PS4', 'PSP', 'PSV') THEN 'SONY'
          WHEN platforms.name IN ('Wii', 'WiiU', 'DS', '3DS') THEN 'Nintendo'
          WHEN platforms.name IN ('X360', 'XONE') THEN 'Microsoft'
          ELSE NULL
      END AS major_platform

    FROM games
        INNER JOIN companies ON games.publisher_id = companies.company_id
        INNER JOIN platforms ON games.platform_id = platforms.platform_id
    WHERE year >= '2012'
    ORDER BY games.name
)

SELECT
    game_name AS name
FROM major_platform_game
GROUP BY game_name
HAVING COUNT(DISTINCT major_platform) >= 2
ORDER BY name



-----------------------------------------------------------------
-----------------------------------------------------------------



# 윈도우 함수 활용

WITH major_platform_game AS (
    SELECT DISTINCT
        games.name AS game_name,
        CASE 
            WHEN platforms.name IN ('PS3', 'PS4', 'PSP', 'PSV') THEN 'SONY'
            WHEN platforms.name IN ('Wii', 'WiiU', 'DS', '3DS') THEN 'Nintendo'
            WHEN platforms.name IN ('X360', 'XONE') THEN 'Microsoft'
            ELSE 'Other'
        END AS major_platform
    FROM games
    INNER JOIN companies ON games.publisher_id = companies.company_id
    INNER JOIN platforms ON games.platform_id = platforms.platform_id
    WHERE year >= '2012'
)

, deduplicated AS (
    SELECT DISTINCT
        game_name,
        major_platform
    FROM major_platform_game
    WHERE major_platform != 'Other'
)

, platform_count AS (
    SELECT
        game_name AS name,
        ROW_NUMBER() OVER (PARTITION BY game_name ORDER BY game_name) AS row_num
    FROM deduplicated
)

SELECT DISTINCT name
FROM platform_count
WHERE row_num != 1;