문제

https://solvesql.com/problems/predict-game-scores-1/

풀이 코드

WITH cte_avg AS(
  SELECT
    genre_id,
    ROUND(AVG(critic_score),3) AS critic_score_avg,
    CEIL(AVG(critic_count)) AS critic_count_avg,
    ROUND(AVG(user_score),3) AS user_score_avg,
    CEIL(AVG(user_count)) AS user_count_avg
  FROM
  games
  WHERE 1=1
    AND critic_score IS NOT NULL
    OR critic_count IS NOT NULL
    OR user_score IS NOT NULL
    OR user_count IS NOT NULL
  GROUP BY genre_id
)

SELECT
  game_id,
  name,
  COALESCE(critic_score,critic_score_avg) AS critic_score,
  COALESCE(critic_count,critic_count_avg) AS critic_count,
  COALESCE(user_score,user_score_avg) AS user_score,
  COALESCE(user_count,user_count_avg) AS user_count
FROM
  games
  INNER JOIN cte_avg
    ON games.genre_id = cte_avg.genre_id
WHERE 1=1
  AND year>=2015
  AND (critic_score IS NULL OR user_score IS NULL)

후기

문제에서 사용자/평론가 수는 올림으로 하라고 했는데 ROUND 함수로 했다가 계속 틀림 ...!!!
올림은 CEIL!