게임 평점 예측하기 1 - solvesql

게임 평점 예측하기 1

출처 : 게임 평점 예측하기 1 SolveSQL

내 답안📕

WITH new_table AS (
  SELECT genre_id
      , ROUND(AVG(critic_score), 3) AS critic_score
      , CEIL(AVG(critic_count)) AS critic_count
      , ROUND(AVG(user_score), 3) AS user_score
      , CEIL(AVG(user_count)) AS user_count
  FROM games
  GROUP BY genre_id
)

SELECT g.game_id
     , g.name
     , CASE WHEN g.critic_score IS NULL THEN nt.critic_score ELSE g.critic_score END AS 'critic_score'
     , CASE WHEN g.critic_count IS NULL THEN nt.critic_count ELSE g.critic_count END AS 'critic_count'
     , CASE WHEN g.user_score IS NULL THEN nt.user_score ELSE g.user_score END AS 'user_score'
     , CASE WHEN g.user_count IS NULL THEN nt.user_count ELSE g.user_count END AS 'user_count'
FROM games AS g
  INNER JOIN new_table AS nt ON g.genre_id = nt.genre_id
WHERE g.year >= 2015
  AND NOT (g.critic_score IS NOT NULL AND g.critic_count IS NOT NULL AND g.user_score IS NOT NULL AND g.user_count IS NOT NULL)