[solvesql - day 17] 멀티 플랫폼 게임 찾기

https://solvesql.com/problems/multiplatform-games/

https://solvesql.com/problems/multiplatform-games/

-- platform_id
-- sony : 14, 15, 16, 17
-- nintendo : 1, 3, 22, 23
-- microsoft : 24, 26
-- 데이터 베이스에 'XONE' 이 'Xone'이라 되어 있어 platform_id인 26을 넣을시 오류 존재

-- 2012년 이후 주요 플랫폼(소니, 닌텐도, 마이크로소프트)에서 출시된 게임을 식별하고
-- 최소 2개 이상의 주요 플랫폼에서 출시된 게임 목록을 조회

WITH sony AS (
    -- 소니(Sony)의 주요 플랫폼에서 출시된 게임 목록
    SELECT 
        game_id,         -- 게임 ID
        name,            -- 게임 이름
        platform_id,     -- 플랫폼 ID
        1 AS major       -- 주요 플랫폼 표시(소니에서 출시된 경우 major=1로 설정)
    FROM 
        games
    WHERE 
        year >= 2012     -- 2012년 이후 출시된 게임만 필터링
        AND platform_id IN (14, 15, 16, 17) -- 소니 플랫폼 ID
),

nintendo AS (
    -- 닌텐도(Nintendo)의 주요 플랫폼에서 출시된 게임 목록
    SELECT 
        game_id,         -- 게임 ID
        name,            -- 게임 이름
        platform_id,     -- 플랫폼 ID
        1 AS major       -- 주요 플랫폼 표시(닌텐도에서 출시된 경우 major=1로 설정)
    FROM 
        games
    WHERE 
        year >= 2012     -- 2012년 이후 출시된 게임만 필터링
        AND platform_id IN (1, 3, 22, 23) -- 닌텐도 플랫폼 ID
),

microsoft AS (
    -- 마이크로소프트(Microsoft)의 주요 플랫폼에서 출시된 게임 목록
    SELECT 
        game_id,         -- 게임 ID
        name,            -- 게임 이름
        platform_id,     -- 플랫폼 ID
        1 AS major       -- 주요 플랫폼 표시(마이크로소프트에서 출시된 경우 major=1로 설정)
    FROM 
        games
    WHERE 
        year >= 2012     -- 2012년 이후 출시된 게임만 필터링
        AND platform_id IN (24) -- 마이크로소프트 플랫폼 ID (XONE 'Xone'으로 입력된 문제로 ID 26 제외)
)

-- 주요 플랫폼별로 게임 데이터를 결합하고 조건을 충족하는 게임 이름 추출
SELECT 
    games.name          -- 최소 2개 주요 플랫폼에서 출시된 게임 이름
FROM 
    games
LEFT JOIN 
    sony                -- 소니 플랫폼 게임과 매칭
ON 
    games.name = sony.name
LEFT JOIN 
    nintendo            -- 닌텐도 플랫폼 게임과 매칭
ON 
    games.name = nintendo.name
LEFT JOIN 
    microsoft           -- 마이크로소프트 플랫폼 게임과 매칭
ON 
    games.name = microsoft.name
GROUP BY 
    games.name          -- 게임 이름별로 그룹화
HAVING 
    (COALESCE(sony.major, 0) + COALESCE(nintendo.major, 0) + COALESCE(microsoft.major, 0)) >= 2
    -- 주요 플랫폼(소니, 닌텐도, 마이크로소프트) 중 최소 2개 이상에서 출시된 게임만 선택