[SQL] solvesql Advent of SQL 2024 9일차

# 링크

• solvesql Advent of SQL 2024: https://solvesql.com/collections/advent-of-sql-2024/

https://solvesql.com/collections/advent-of-sql-2024/

• 9일차 - 게임 평점 예측하기 1: https://solvesql.com/problems/predict-game-scores-1/

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

 

# 풀이

WITH GenreAverages AS (
  SELECT
    genre_id,
    ROUND(AVG(critic_score),3) AS avg_critic_score,
    CAST(AVG(critic_count) AS INTEGER) +
    (CASE WHEN AVG(critic_count) > CAST(AVG(critic_count) AS INTEGER) THEN 1 ELSE 0 END)
    AS avg_critic_count,
    ROUND(AVG(user_score),3) AS avg_user_score,
    CAST(AVG(user_count) AS INTEGER) +
    (CASE WHEN AVG(user_count) > CAST(AVG(user_count) AS INTEGER) THEN 1 ELSE 0 END)
    AS avg_user_count
  FROM games
  GROUP BY genre_id
),
MissingRatings AS (
  SELECT
    g.game_id,
    g.name,
    COALESCE(g.critic_score, ga.avg_critic_score) AS critic_score,
    COALESCE(g.critic_count, ga.avg_critic_count) AS critic_count,
    COALESCE(g.user_score, ga.avg_user_score) AS user_score,
    COALESCE(g.user_count, ga.avg_user_count) AS user_count
  FROM games g
  LEFT JOIN GenreAverages ga
  ON g.genre_id = ga.genre_id
  WHERE g.year >= 2015
    AND (g.critic_score IS NULL
      OR g.critic_count IS NULL
      OR g.user_score IS NULL
      OR g.user_count IS NULL)
)
SELECT
  game_id,
  name,
  ROUND(critic_score, 3) AS critic_score,
  critic_count,
  ROUND(user_score, 3) AS user_score,
  user_count
FROM MissingRatings

• GenreAverages CTE
  • SELECT
    • genre_id: genre_id 컬럼을 선택한다. 
    • ROUND(AVG(critic_score),3) AS avg_critic_score: critic_score의 평균을 구하여 소수점 셋째 자리까지 반올림하고, avg_critic_score로 저장한다.
    • CAST(AVG(critic_count) AS INTEGER) + (CASE WHEN AVG(critic_count) > CAST(AVG(critic_count) AS INTEGER) THEN 1 ELSE 0 END) AS avg_critic_count: critic_count의 평균의 정수 부분에 critic_count의 평균값이 critic_count 평균의 정수 부분보다 클 경우에만 1을 더하고(올림 처리), avg_critic_count로 저장한다.
    • ROUND(AVG(user_score),3) AS avg_user_score: user_score의 평균을 구하여 소수점 셋째 자리까지 반올림하고, avg_user_score로 저장한다.
    • CAST(AVG(user_count) AS INTEGER) + (CASE WHEN AVG(user_count) > CAST(AVG(user_count) AS INTEGER) THEN 1 ELSE 0 END) AS avg_user_count: user_count의 평균의 정수 부분에 user_count의 평균값이 user_count 평균의 정수 부분보다 클 경우에만 1을 더하고(올림 처리), avg_user_count로 저장한다.
  • FROM games: games 테이블에서 데이터를 불러온다.
  • GROUP BY genre_id: genre_id를 기준으로 그룹화한다.
• Missing Ratings CTE
  • SELECT
    • g.name: games 테이블에서 name 컬럼을 선택한다.
    • COALESCE(g.critic_score, ga.avg_critic_score) AS critic_score: games 테이블의 critic_score가 NULL인 경우, GenreAverages CTE의 avg_critic_score로 채우고, critic_score로 저장한다.
    • COALESCE(g.critic_count, ga.avg_critic_count) AS critic_count: games 테이블의 critic_count가 NULL인 경우, GenreAverages CTE의 avg_critic_count로 채우고, critic_count로 저장한다.
    • COALESCE(g.user_score, ga.avg_user_score) AS user_score: games 테이블의 user_score가 NULL인 경우, GenreAverages CTE의 avg_user_score로 채우고, user_score로 저장한다.
    • COALESCE(g.user_count, ga.avg_user_count) AS user_count: games 테이블의 user_count가 NULL인 경우, GenreAverages CTE의 avg_user_count로 채우고, user_count로 저장한다.  
  • FROM games g: games 테이블에 g라는 별칭을 붙이고, games 테이블에서 데이터를 불러온다.
  • LEFT JOIN GenreAverages ga ON g.genre_id = ga.genre_id: GenreAverages CTE에 ga라는 별칭을 붙이고, games 테이블과 GenreAverages CTE를 genre_id를 기준으로 조인한다. LEFT JOIN을 사용하여 games에 있는 모든 게임을 포함하고, GenreAverages에서 해당 장르에 맞는 평균값을 가져온다.
  • WHERE g.year >= 2015 AND (g.critic_score IS NULL OR critic_count IS NULL OR user_score IS NULL OR user_count IS NULL): games 테이블의 year의 값이 2015 이상이고, games 테이블의 critic_score, critic_count, user_score, user_count 중 하나라도 NULL인 데이터만 선택한다.
• SELECT
  • game_id: game_id 컬럼을 선택한다.
  • name: name 컬럼을 선택한다.
  • ROUND(critic_score, 3) AS critic_score: critic_score를 소수점 셋째 자리까지 반올림하고, critic_score로 저장한다.
  • critic_count: critic_count 컬럼을 선택한다.
  • ROUND(user_score, 3) AS user_score: user_score를 소수점 셋째 자리까지 반올림하고, user_score로 저장한다.
  • user_count: user_count 컬럼을 선택한다.
• FROM MissingRatings: MissingRatings CTE에서 데이터를 불러온다.