멀티 플랫폼 게임 찾기 - SolveSQL

멀티 플랫폼 게임 찾기

출처 : 멀티 플랫폼 게임 찾기 SolveSQL

내 답안

SELECT game_name AS name
FROM (SELECT G.name AS game_name
            , P.platform_name AS platform_name
      FROM games AS G
      INNER JOIN (SELECT platform_id
                       , CASE 
                             WHEN name IN ('PS3', 'PS4', 'PSP', 'PSV') THEN 'Sony'
                             WHEN name IN ('X360', 'XONE') THEN 'Microsoft'
                             ELSE 'Nintendo'
                         END AS 'platform_name'
                         FROM platforms
                         WHERE name IN ('PS3', 'PS4', 'PSP', 'PSV', 'Wii', 'WiiU', 'DS', '3DS', 'X360', 'XONE')
                 ) AS P ON G.platform_id = P.platform_id
      WHERE G.year >= '2012-01-01 00:00:00'
      GROUP BY game_name, platform_name
) AS popular_game
GROUP BY game_name
  HAVING COUNT(*) >= 2
ORDER BY name ASC;

DISTINCT 쓰는걸 생각못해서, 코드가 이렇게 길어졌다..