solvesql에서 제공해주는 연말 sql 문제로 모든 문제를 풀면 데이터리안 수강권 10만원 할인 쿠폰을 제공해준다고 한다. 물론 나는 늦게 알아서 할인 쿠폰 받기는 어렵지만, 풀었던 문제들 중에 어려웠던 문제가 있어서 가져와보았다.
✅문제
Video Game Sales with Ratings 데이터베이스에는 2016년까지 발매된 게임의 주요 정보와 판매량, 평점 정보가 담겨 있습니다. games 테이블에 들어있는 평점 정보는 평론가 평점(critic_score), 평점을 남긴 평론가 수(critic_count), 사용자 평점(user_score), 평점을 남긴 사용자 수(user_count)로 구성되어 있습니다.
평점 정보에 일부 누락이 있음을 알게된 당신은 같은 장르를 가진 게임들의 평균 평점과 평균 평론가/사용자 수를 활용해 누락된 값을 채우려고 합니다. 2015년 이후에 발매한 게임 중 누락된 평점 정보가 있는 게임에 대해서 같은 장르를 가진 전체 게임의 평균 평점과 평균 평론가/사용자 수를 사용해 누락된 정보를 채우는 쿼리를 작성해주세요. 쿼리 결과에는 누락된 평점 정보가 있는 게임만 포함되어야 하며, 아래 6개 컬럼이 있어야 합니다. 또한, 평론가와 사용자 평점 평균은 소수점 아래 넷째 자리에서 반올림 해 셋째 자리까지 출력되어야 하고, 사용자 수는 올림하여 자연수로 출력되어야 합니다.
✅컬럼 설명
game_id: 게임 ID
name: 게임 이름
critic_score: 평론가 평점 평균
critic_count: 평점을 남긴 평론가 수
user_score: 사용자 평점 평균
user_count: 평점을 남긴 사용자 수
✅결과 데이터 예시
✅정답 (테스트 케이스 통과 코드)
with cte_1 as (select genre_id, name,game_id,critic_score, critic_count, user_score, user_count
from games where (critic_score is null or user_score is null) and year >= 2015 ),
cte_2_1 as (
select genre_id,
round(avg(critic_score),3) as critic_score,
ceil(avg(critic_count)) as critic_count
from games
where critic_score is not null
group by genre_id),
cte_2_2 as (
select genre_id,
round(avg(user_score),3) as user_score,
ceil(avg(user_count)) as user_count
from games where user_score is not null
group by genre_id)
select
c1.game_id,c1.name,
COALESCE(c1.critic_score , c2_1.critic_score) as critic_score,
COALESCE(c1.critic_count , c2_1.critic_count) as critic_count,
COALESCE(c1.user_score , c2_2.user_score) as user_score,
COALESCE(c1.user_count , c2_2.user_count) as user_count
from cte_1 c1
join cte_2_1 c2_1 on c1.genre_id = c2_1.genre_id
join cte_2_2 c2_2 on c1.genre_id = c2_2.genre_id
order by 1 ;출력 결과
총 658개 행
✅풀이 과정 (주의해야할 점은?)
- cte_1 : 2015년 이후이면서 critic또는 user 컬럼 데이터가 존재하지 않았을 때 테이블
- cte_2_1 : critic데이터가 null값이 아닐때 평균 critic score, 반올림 critic count
- cte_2_2 : user데이터가 null값이 아닐때 평균 user score, 반올림 user count
주의사항 1
📌critic데이터가 null값이 아니면서 user데이터가 null값이 아닌 데이터를 하나의 테이블로 만들어서 진행하지 말 것
📌critic 데이터가 없을 때 채워넣거나 , user데이터가 없을 때 채워넣어야함 (📍따로 따로 봐야한다!📍)
--> critic null 값 없는 데이터 따로, user 데이터 null 값 없는 데이터 따로 보기!!
--> 그리고 join해서 null값 있는 데이터에 결측값이 없었을 때의 정보 평균, 반올림한 데이터를 대체해야한다.
그래서 나는 cte_2_1 데이터를 critic 결측값이 없을 때 데이터, cte_2_2 데이터를 user 결측값이 없을 때 데이터로 보고서 채워주는 작업을 진행했다.
주의사항 2
📌결측값이 있을 때 2015년 이후 데이터 인 것이지, 결측값이 없을 때 데이터는 2015년이 아니다.
📌결측값 없는 데이터 출력할 때 조건절에 where year>=2015 걸면 안된다.
✅새로 알게 된 함수
✔ 1. COALESCE(채워넣을 컬럼, 결측값 있을 때 채워넣고 싶은 컬럼)
IFNULL이랑 같은 기능을 한다.
✔ 2. CEIL(컬럼)
반올림해준다.
