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

silver

Mar 12, 2025

Contents

문제 SQLite

문제

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)

내가 작성한 정답

: 위의 쿼리에서 g.critic_score is null and g.user_score is null 부분을 and가 아닌 or로 변경하고 평론가 데이터만 존재하는 경우와 사용자 데이터만 존재하는 경우 존재하는 데이터를 가져오기 위해 ifnull을 사용했다.


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)

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)