[Day 17] solvesql | Advent of SQL 2024 | 멀티 플랫폼 게임 찾기

멀티 플랫폼 게임 찾기

✔️0212 1116 복습

문제 조건

1. 2012년 이후 출시된 게임들 중 둘 이상의 메이저 플랫폼 계열에 출시된 게임 이름을 출력

2. 중복된 게임은 1번만 출력

 

메이저 플랫폼 계열:

• Sony: 'PS3', 'PS4', 'PSP', 'PSV'
• Nintendo: 'Wii', 'WiiU', 'DS', '3DS'
• Microsoft: 'X360', 'XONE'

 

Solution

-- 가장 최근 쿼리
WITH major_platforms AS (
  -- 관심있는 플랫폼들만 필터링
  SELECT *
  FROM platforms
  WHERE name IN ('PS3','PS4','PSP','PSV','Wii','WiiU','DS','3DS','X360','XONE')

), major_games AS (
  -- 그중 경우에 따른 플랫폼 분류 & 2012년 이후 출시된 게임
  SELECT g.name
       , CASE WHEN p.name IN ('PS3','PS4','PSP','PSV') THEN 'Sony'
              WHEN p.name IN ('Wii','WiiU','DS','3DS') THEN 'Nintendo'
              ELSE 'Microsoft'
         END AS major
  FROM games g
  JOIN major_platforms p ON g.platform_id = p.platform_id
  WHERE g.year >= 2012

)
-- 둘 이상의 메이저 플랫폼 계열에 출시된 게임 출력
SELECT name
FROM major_games
GROUP BY name
HAVING COUNT(DISTINCT major) > 1

WITH T1 AS (
  SELECT *,
         CASE WHEN name IN ('PS3', 'PS4', 'PSP', 'PSV') THEN 'Sony'
              WHEN name IN ('Wii', 'WiiU', 'DS', '3DS') THEN 'Nintendo'
              WHEN name IN ('X360', 'XONE') THEN 'Microsoft'
              ELSE 'Others' END as target
  FROM platforms
  )
  SELECT DISTINCT g.name
  FROM games g
  JOIN T1 t ON g.platform_id = t.platform_id
  WHERE g.year >= 2012 AND t.target IN ('Sony', 'Nintendo', 'Microsoft')
  GROUP BY g.name
  HAVING COUNT(DISTINCT target) >= 2
  
/*
  XONE -> XOne 오타, 문제 수정 필요해보임 -> 반영된 듯
*/
WITH step1 AS (
  SELECT g.name
       , g.platform_id
       , p.name AS p_name
       , CASE WHEN p.name IN ('PS3', 'PS4', 'PSP', 'PSV') THEN 'Sony'
              WHEN p.name IN ('Wii', 'WiiU', 'DS', '3DS') THEN 'Nintendo'
              ELSE 'Microsoft'
         END AS is_major -- 메이저 플랫폼 컬럼 가공
  FROM games g
    JOIN ( -- 관심있는 플랫폼만 부분 JOIN할게~
          SELECT *
          FROM platforms
          WHERE name IN ('PS3', 'PS4', 'PSP', 'PSV', 'Wii', 'WiiU', 'DS', '3DS', 'X360', 'XONE')
         ) p ON g.platform_id = p.platform_id
  WHERE g.year >= 2012

)
SELECT name
FROM step1
-- WHERE name = '7 Days to Die' -- 'XOne' 플랫폼에 속한 애들 개수때문에 정답 처리가 안됐던 것
GROUP BY name
HAVING COUNT(DISTINCT is_major) >= 2 -- 둘 이상의 메이저 플랫폼에 속하는 조건

 

▶ T1 CTE:

CASE문을 사용하여 메이저 플랫폼 계열에 속하는 플랫폼을 매핑

 

▶ 최종 SELECT:

2012년 이후 출시된 게임 중 둘 이상의 메이저 플랫폼 계열에 출시된 게임 이름을 추출

 

강사님

SELECT name
FROM (
  SELECT g.name
       , COUNT(DISTINCT CASE
                            WHEN p.name IN ('PS3', 'PS4', 'PSP', 'PSV') THEN 'Sony'
                            WHEN p.name IN ('Wii', 'WiiU', 'DS', '3DS') THEN 'Nintendo'
                            WHEN p.name IN ('X360', 'XONE') THEN 'Microsoft'
                        END) AS cnt -- 나머지는 null로 두겠네
      --  , p.name
  FROM games g
    JOIN platforms p ON g.platform_id = p.platform_id
  WHERE g.year >= 2012
  GROUP BY g.name
  HAVING cnt > 1
) AS t