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

문제

solvesql.com

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

SQLite

내가 작성한 오답

평론가 평점 데이터만 존재하지 않는 경우

사용자 평점 데이터만 존재하지 않는 경우

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

내가 작성한 정답

with a as (select round(avg(critic_score),3) critic_score, 
                  ceiling(avg(critic_count)) critic_count,
                  round(avg(user_score),3) user_score,
                  ceiling(avg(user_count)) user_count,
                  genre_id
          from games
          group by genre_id)
select 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 a.genre_id = g.genre_id
where g.year >= 2015
  and (g.critic_score is null or g.user_score is null)