[SQL문제풀기] 멀티 플랫폼 게임 찾기

문제

solvesql.com

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

SQLite

내가 작성한 오답

'Sony','Nintendo','Microsoft'은 제작사였고 그 옆이 플랫폼이었다. 제작사가 같으면 같은 플랫폼 계열로 묶는 거였다.

select distinct g.name
from games g
join platforms p on g.platform_id = p.platform_id 
												and p.name in ('Sony','Nintendo','Microsoft')
where g.year >= 2012
group by g.name
having count(p.platform_id) >= 2

내가 작성한 정답

select distinct g.name
from games g
join platforms p on g.platform_id = p.platform_id 
      and p.name in ('PS3', 'PS4', 'PSP', 'PSV', 'Wii', 'WiiU', 'DS', '3DS','X360', 'XONE')
where g.year >= 2012
group by g.name
having 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) >= 2

select g.name
from games g
join platforms p on g.platform_id = p.platform_id 
      and p.name in ('PS3', 'PS4', 'PSP', 'PSV', 'Wii', 'WiiU', 'DS', '3DS','X360', 'XONE')
where g.year >= 2012
group by g.name
having count (distinct case 
        when p.name in ('PS3', 'PS4', 'PSP', 'PSV') then 'Sony'
        when p.name in ('Wii', 'WiiU', 'DS', '3DS') then 'Nintendo'
        else 'Microsoft' end) >= 2