[solvesql - day 9] 게임 평점 예측하기 1

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

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

-- 'games' 테이블에서 각 장르별 평균 평점 및 평론/사용자 수를 계산하고, 이를 기준으로 
-- 특정 평론/사용자 정보가 NULL인 게임들에 대해 대체값을 채워주는 쿼리
WITH B AS (
    -- 'games' 테이블에서 각 'genre_id' 별로 평론 점수, 평론 수, 사용자 점수, 사용자 수의 평균을 계산
    SELECT
        genre_id,  -- 장르 ID
        round(AVG(critic_score), 3) AS avg_cs,  -- 각 장르의 평균 평론 점수를 소수점 3자리까지 반올림
        ceil(AVG(critic_count)) AS avg_cc,  -- 각 장르의 평균 평론 수를 올림
        round(AVG(user_score), 3) AS avg_us,  -- 각 장르의 평균 사용자 점수를 소수점 3자리까지 반올림
        ceil(AVG(user_count)) AS avg_uc  -- 각 장르의 평균 사용자 수를 올림
    FROM games
    GROUP BY genre_id  -- 장르 ID별로 그룹화하여 평균 계산
)
-- 'games' 테이블과 위에서 계산한 평균 값을 가진 B 테이블을 조인하여 NULL 값을 채워넣기
SELECT 
    game_id,  -- 게임 ID
    name,  -- 게임 이름
    -- critic_score가 NULL인 경우 B 테이블의 avg_cs로 대체
    CASE 
        WHEN critic_score IS NULL THEN B.avg_cs 
        ELSE critic_score 
    END AS critic_score,  -- 평론 점수
    -- critic_count가 NULL인 경우 B 테이블의 avg_cc로 대체
    CASE 
        WHEN critic_count IS NULL THEN B.avg_cc 
        ELSE critic_count 
    END AS critic_count,  -- 평론 수
    -- user_score가 NULL인 경우 B 테이블의 avg_us로 대체
    CASE 
        WHEN user_score IS NULL THEN B.avg_us 
        ELSE user_score 
    END AS user_score,  -- 사용자 점수
    -- user_count가 NULL인 경우 B 테이블의 avg_uc로 대체
    CASE 
        WHEN user_count IS NULL THEN B.avg_uc 
        ELSE user_count 
    END AS user_count  -- 사용자 수
FROM 
    games AS A  -- 'games' 테이블을 A로 alias 설정
JOIN B  -- B 테이블과 A를 genre_id를 기준으로 조인
    ON A.genre_id = B.genre_id
WHERE 
    -- 'critic_score', 'critic_count', 'user_score', 'user_count' 중 하나라도 NULL인 경우에만 결과를 반환
    (A.critic_score IS NULL OR 
     A.critic_count IS NULL OR 
     A.user_score IS NULL OR 
     A.user_count IS NULL)
    AND year >= 2015  -- 2015년 이후에 출시된 게임만 결과에 포함