SQL 문제 풀이 챌린지(9/25) - 게임 평점 예측하기 1

sql/Advent of SQL 2024

SQL 문제 풀이 챌린지(9/25) - 게임 평점 예측하기 1

gooreumsea 2024. 12. 10. 16:39

저작권 문제로 인해, 직접 작성한 쿼리문만 공개.

다음엔 coalesce을 활용해보면 좋을 것 같다.

9. 게임 평점 예측하기 1

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

2015년 이후에 발매한 게임 중 누락된 평점 정보가 있는 게임에 대하여

같은 장르를 가진 전체 게임의 평균 평점과 평균 평론가/사용자 수를 사용해 누락된 정보를 채우는 쿼리 작성

1. 데이터 확인

SELECT
    COUNT(critic_score),
    COUNT(critic_count),
    COUNT(user_score),
    COUNT(user_count)
FROM games
INNER JOIN genres ON games.genre_id = genres.genre_id

2. 경우의 수 확인

1) 2개 컬럼 critic_score, critic_count 가 결측치 일때,
2) 2개 컬럼 user_score, user_count 가 결측치 일때,
3) 4개 컬럼 critic_score, critic_count, user_score, user_count 이 모두 결측치 일때

따라서 원본테이블을 3가지로 분할 후 누락된 데이터를 INNER 조인하여 값을 붙인 뒤 3개 테이블을 병합
(데이터 누락이 없는 행은 문제에서 필요로 하지 않음)

-- 누락 정보 조인을 위한 테이블

WITH fillna_critic_info AS (
SELECT
games.genre_id AS genre_id,
ROUND(AVG(critic_score),3) AS AVG_critic_score,
CEIL(AVG(critic_count)) AS AVG_critic_count

FROM games
INNER JOIN genres ON games.genre_id = genres.genre_id
GROUP BY games.genre_id
),


fillna_user_info AS (
SELECT
games.genre_id AS genre_id,
ROUND(AVG(user_score),3) AS AVG_user_score,
CEIL(AVG(user_count)) AS AVG_user_count

FROM games
INNER JOIN genres ON games.genre_id = genres.genre_id
GROUP BY games.genre_id
),


fillna_all_info AS (
SELECT
games.genre_id AS genre_id,
ROUND(AVG(critic_score),3) AS AVG_critic_score,
CEIL(AVG(critic_count)) AS AVG_critic_count,
ROUND(AVG(user_score),3) AS AVG_user_score,
CEIL(AVG(user_count)) AS AVG_user_count

FROM games
INNER JOIN genres ON games.genre_id = genres.genre_id
GROUP BY games.genre_id
),

-- 3개 테이블로 분할

all_null AS(

-- 1. ALL IS NULL
SELECT
games.genre_id,
game_id,
games.name


FROM games
INNER JOIN genres ON games.genre_id = genres.genre_id


WHERE critic_score IS NULL
AND critic_count IS NULL
AND user_score IS NULL
AND user_count IS NULL
AND year >= 2015
),


missing_user_info AS (

-- 2.critic_score, count IS NOT.NULL
SELECT
games.genre_id,
game_id,
games.name,
critic_score,
critic_count

FROM games
INNER JOIN genres ON games.genre_id = genres.genre_id

WHERE critic_score IS NOT NULL
AND critic_count IS NOT NULL
AND user_score IS NULL
AND user_count IS NULL
AND year >= 2015
),


missing_critic_info AS(

-- 3.user_score, count IS NOT.NULL
SELECT
games.genre_id,
game_id,
games.name,
user_score,
user_count
FROM games
INNER JOIN genres ON games.genre_id = genres.genre_id

WHERE critic_score IS NULL
AND critic_count IS NULL
AND user_score IS NOT NULL
AND user_count IS NOT NULL
AND year >= 2015
)

3. 누락정보 INNER 조인 + UNION ALL

-- 유저 정보 필링
SELECT
missing_user_info.game_id,
name,
critic_score,
critic_count,
AVG_user_score AS user_score,
AVG_user_count AS user_count

FROM missing_user_info
INNER JOIN fillna_user_info ON missing_user_info.genre_id = fillna_user_info.genre_id

UNION ALL

-- 평론가 정보 필링

SELECT
missing_critic_info.game_id,
name,
AVG_critic_score AS critic_score,
AVG_critic_count AS critic_count,
user_score,
user_count

FROM missing_critic_info
INNER JOIN fillna_critic_info ON missing_critic_info.genre_id = fillna_critic_info.genre_id

UNION ALL

-- 유저 & 평론가 정보 필링

SELECT
all_null.game_id,
name,
AVG_critic_score AS critic_score,
AVG_critic_count AS critic_count,
AVG_user_score AS user_score,
AVG_user_count AS user_count

FROM all_null
INNER JOIN fillna_all_info ON all_null.genre_id = fillna_all_info.genre_id

#. 전체쿼리

2015년 이후에 발매한 게임 중 누락된 평점 정보가 있는 게임에 대하여
같은 장르를 가진 전체 게임의 평균 평점과 평균 평론가/사용자 수를 사용해 누락된 정보를 채우는 쿼리 작성


-- 데이터 확인

-- SELECT 
--   COUNT(critic_score),
--   COUNT(critic_count),
--   COUNT(user_score),
--   COUNT(user_count)
-- FROM games
-- INNER JOIN genres ON games.genre_id = genres.genre_id




-- 누락 정보 조인을 위한 테이블

WITH fillna_critic_info AS (
  SELECT
      games.genre_id AS genre_id,
      ROUND(AVG(critic_score),3) AS AVG_critic_score,      
      CEIL(AVG(critic_count)) AS AVG_critic_count

  FROM games
  INNER JOIN genres ON games.genre_id = genres.genre_id
  GROUP BY games.genre_id
), 


fillna_user_info AS (
  SELECT
      games.genre_id AS genre_id,
      ROUND(AVG(user_score),3) AS AVG_user_score,      
      CEIL(AVG(user_count)) AS AVG_user_count

  FROM games
  INNER JOIN genres ON games.genre_id = genres.genre_id
  GROUP BY games.genre_id
), 


fillna_all_info AS (
  SELECT
      games.genre_id AS genre_id,
      ROUND(AVG(critic_score),3) AS AVG_critic_score,      
      CEIL(AVG(critic_count)) AS AVG_critic_count,
      ROUND(AVG(user_score),3) AS AVG_user_score,      
      CEIL(AVG(user_count)) AS AVG_user_count

  FROM games
  INNER JOIN genres ON games.genre_id = genres.genre_id
  GROUP BY games.genre_id
),   



-- 3개 테이블로 분할

all_null AS(

-- 1. ALL IS NULL
SELECT
  games.genre_id,
  game_id,
  games.name


FROM games
INNER JOIN genres ON games.genre_id = genres.genre_id


WHERE critic_score IS NULL
AND critic_count IS NULL
AND user_score IS NULL
AND user_count IS NULL
AND year >= 2015
),


missing_user_info AS (

-- 2.critic_score, count IS NOT.NULL
  SELECT
    games.genre_id,
    game_id,
    games.name,
    critic_score,
    critic_count

  FROM games
  INNER JOIN genres ON games.genre_id = genres.genre_id

  WHERE critic_score IS NOT NULL
  AND critic_count IS NOT NULL
  AND user_score IS NULL
  AND user_count IS NULL
  AND year >= 2015
),


missing_critic_info AS(

-- 3.user_score, count IS NOT.NULL 
  SELECT
    games.genre_id,
    game_id,
    games.name,
    user_score,
    user_count
  FROM games
  INNER JOIN genres ON games.genre_id = genres.genre_id

  WHERE critic_score IS NULL
  AND critic_count IS NULL
  AND user_score IS NOT NULL
  AND user_count IS NOT NULL
  AND year >= 2015
)



-- 누락정보 INNER 조인 + UNION ALL

-- 유저 정보 필링
SELECT
  missing_user_info.game_id,
  name,
  critic_score,
  critic_count,
  AVG_user_score AS user_score,
  AVG_user_count AS user_count

FROM missing_user_info
INNER JOIN fillna_user_info ON missing_user_info.genre_id = fillna_user_info.genre_id

UNION ALL

-- 평론가 정보 필링

SELECT
  missing_critic_info.game_id,
  name,
  AVG_critic_score AS critic_score,
  AVG_critic_count AS critic_count,
  user_score,
  user_count

FROM missing_critic_info
INNER JOIN fillna_critic_info ON missing_critic_info.genre_id = fillna_critic_info.genre_id

UNION ALL

-- 유저 & 평론가 정보 필링

SELECT
  all_null.game_id,
  name,
  AVG_critic_score AS critic_score,
  AVG_critic_count AS critic_count,
  AVG_user_score AS user_score,
  AVG_user_count AS user_count

FROM all_null
INNER JOIN fillna_all_info ON all_null.genre_id = fillna_all_info.genre_id