[SQL문제풀기] 게임 평점 예측하기 1

문제

solvesql.com

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

SQLite

내가 작성한 정답

with a as (select distinct genre_id, 
                  round(avg(critic_score),3)critic_score,
                  ceil(avg(critic_count)) critic_count,
                  round(avg(user_score),3) user_score, ceil(avg(user_count)) user_count
            from games
            group by genre_id)
select distinct g.game_id, g.name,
      ifnull(g.critic_score,a.critic_score) critic_score,
      ifnull(g.critic_count, a.critic_count) critic_count,
      ifnull(g.user_score, a.user_score) user_score,
      ifnull(g.user_count, a.user_count) user_count
from games g
left join a a on g.genre_id = a.genre_id
where g.year >= 2015
  and (g.critic_score is null or g.user_score is null);