문제

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

풀이

WITH filtering AS (
  SELECT
    g.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 majorPlatform
  FROM
    games g
    LEFT JOIN platforms p 
      ON g.platform_id = p.platform_id
  WHERE
    g.year >= 2012
)
SELECT
  name
FROM
  filtering
GROUP BY
  name
HAVING 1=1
  AND COUNT(DISTINCT majorPlatform) >= 2 

핵심

1. 하나의 게임이 같은 플랫폼에 들어가 있을 수 있기 때문에 DISTINCT majorPlatform를 해준다.
2. 2021년 이후