[Solvesql - Advent of SQL 2024] 게임 평점 예측하기 1

1. 문제

2015년 이후 장르가 동일한 평균 평점으로 누락 평점 채우기

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

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

 

 

2. 접근

1) 상관 서브쿼리(Correlated Subquery)

메인 쿼리에서 e를 서브쿼리로 넣어서 하나씩 비교한다. 

 

SELECT employee_id, name, salary
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
);

 

 

2) 반올림, 내림, 올림 

- 반올림 (ROUND) 

SELECT ROUND(123.456, 2) AS rounded1,  -- 소수점 둘째 자리까지 반올림
       ROUND(123.456, 0) AS rounded2,  -- 정수로 반올림
       ROUND(123.456, -1) AS rounded3; -- 소수점 이전 자리에서 반올림

 

결과:

123.46

123

120

 

 

내림 (FLOOR)

SELECT FLOOR(123.456) AS floored1,  -- 소수점 아래 버림
       FLOOR(-123.456) AS floored2; -- 음수도 내림

 

- 올림(CEIL)

SELECT CEIL(123.456) AS ceiled1,   -- 소수점 올림
       CEIL(-123.456) AS ceiled2; -- 음수도 올림

 

FLOOR, CEIL는 정수로 내리거나 올리는거다. 

그럼 소수점에는 어떡하지? 

소수점 둘째 자리에서 내림이나 올림하는 방법은 * 100 / 100을 이용하는거다. 

#내림
SELECT FLOOR(123.456 * 100) / 100 AS result;

#올림
SELECT CEIL(123.456 * 100) / 100 AS result;

 

 

3. 전체코드

select
  e.game_id,
  e.name,
  Round(
    (
      case
        when critic_score is null then (
          select
            avg(critic_score)
          from
            games as b
          where
            e.genre_id = b.genre_id
        )
        else critic_score
      end
    ),
    3
  ) as critic_score,
  CEIL(
    (
      case
        when critic_count is null then (
          select
            avg(critic_count)
          from
            games as b
          where
            e.genre_id = b.genre_id
        )
        else critic_count
      end
    )
  ) as critic_count,
  Round(
    (
      case
        when user_score is null then (
          select
            avg(user_score)
          from
            games as b
          where
            e.genre_id = b.genre_id
        )
        else user_score
      end
    ),
    3
  ) as user_score,
  CEIL(
    (
      case
        when user_count is null then (
          select
            avg(user_count)
          from
            games as b
          where
            e.genre_id = b.genre_id
        )
        else user_count
      end
    )
  ) as user_count
from
  games as e
where
  e.year >= 2015
  and (
    e.critic_score is null
    or e.critic_count is null
    or e.user_score is null
    or e.user_count is null
  );