SQL | SOVESQL 2024 🎄: Day 17. 멀티 플랫폼 게임 찾기

Day 17. 멀티 플랫폼 게임 찾기

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

-- 같은 이름이어도 년도다 아이디가 다름

with t1 as (
  select g.game_id, g.name as game_name
    , year
    , p.platform_id, p.name as platform_name
    , 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 'major_p'
  from games as g 
    left join platforms as p on g.platform_id = p.platform_id
  where year >= 2012
  order by g.name, year
), t2 as (
  select game_name, major_p
    , count(major_p) over(PARTITION by game_name) as major_cnt
  from t1
  where major_p is not null
  group by game_name, major_p
)

select DISTINCT game_name as name
from t2
where major_cnt >=2

❗WHERE 절에 메이저 플랫폼 개수가 2개 이상인 것만 추출

 

 

 

+) WITH 절 : T1

❗CASE WHEN 절에 게임기에 따라 플랫폼 칼럼 만들기
❗LEFT JOIN 사용해서 게임과 플랫폼 연결

 

 

 

+) WITH 절 : T2

❗윈도우 함수 사용해서 게임 이름에 따라 메이저 플랫폼 갯수 세기
❗WHERE 절에 메이저 플랫폼인 것만 사용
❗GROUP BY 사용해서 게임 이름과 메이저 플랫폼 기준 만들기