[solvesql] NULL 대체하기

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

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

 

NULL 값을 대체해야 할 땐, COALESCE를 사용할 수 있따!!

with avg_tb as (
  select 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
  group by genre_id
),
filtered_tb as (
  select *
  from games g
  where g.year>=2015 
  and (critic_score is null or critic_count is null or user_score is null or user_count is null)
)

select game_id, name,
        COALESCE(critic_score , a.avg_critic_score) as critic_score,
        COALESCE(critic_count, a.avg_critic_count) as critic_count,
        COALESCE(user_score, a.avg_user_score) as user_score,
        COALESCE(user_count, a.avg_user_count) as user_count
from filtered_tb f
join avg_tb a on f.genre_id = a.genre_id