SQL 코드카타 115 - Movie Rating

https://leetcode.com/problems/movie-rating/description/

 

Column Name	Type
movie_id	int
title	varchar

 

Movies 테이블:

• movie_id : 테이블의 고유 키
• title : 영화의 제목

 

Column Name	Type
user_id	int
name	varchar

 

Users 테이블:

• user_id : 테이블의 고유 키
• name : 사용자의 이름

 

Column Name	Type
movie_id	int
user_id	int
rating	int
created_at	date

 

MovieRating 테이블:

• movie_id : 테이블의 고유 키
• user_id : 테이블의 고유 키
• rating : 영화에 대한 평점
• created_at : 리뷰가 작성된 날짜

 

 

1. 가장 많은 영화를 평가한 사용자의 이름을 찾으세요. 동점일 경우, 사전 순으로 앞서는 사용자의 이름을 반환하세요.
2. 2020년 2월에 가장 높은 평균 평점을 받은 영화의 이름을 찾으세요. 동점일 경우, 사전 순으로 앞서는 영화의 이름을 반환하세요.

select *
from
MovieRating MR
left join Users U on MR.user_id = U.user_id

 

 

먼저 가장 많은 리뷰를 남긴 사용자를 찾기 위해 MovieRating 테이블과 Users테이블을 결합한다.

 

select
    name,
    count(name)
from
MovieRating MR
left join Users U on MR.user_id = U.user_id
group by name
order by count(name) DESC

 

사용자들의 이름이 나타난 횟수를 각각 센 후에 횟수에 대해서 내림차순으로 정렬한다.

 

select
    name,
    count(name)
from
MovieRating MR
left join Users U on MR.user_id = U.user_id
group by name
order by count(name) DESC, name
limit 1

 

이름에 대해서 오름차순으로 정렬한 후 가장 위의 값만을 반환해서 가장 리뷰를 많이 작성한 사용자를 반환할 수 있다.

 

select
    name
from
MovieRating MR
left join Users U on MR.user_id = U.user_id
group by name
order by count(name) DESC, name
limit 1

 

이제는 필요가 없는 count(name)을 삭제해주면 완료.

 

------------------------------------------------------------------------------------------------------------------------------------------------------

select *
from
MovieRating MR
left join Movies M on MR.movie_id = M.movie_id

 

다음으로 2월에 평균평점이 가장 높은 영화를 반환하기 위해서 MovieRating 테이블과 Movies테이블을 결합한다.

 

select *
from
MovieRating MR
left join Movies M on MR.movie_id = M.movie_id
where date_format(MR.created_at, '%m') = '02'

 

where를 사용해서 2월에 남겨진 리뷰만 조회한다.

 

select
    title,
    avg(rating)
from
MovieRating MR
left join Movies M on MR.movie_id = M.movie_id
where date_format(MR.created_at, '%m') = '02'
group by title
order by avg(rating) DESC

 

영화별로 그룹화한 뒤에 영화별 평균 평점으로 내림차순 정렬한다.

 

select
    title
from
MovieRating MR
left join Movies M on MR.movie_id = M.movie_id
where date_format(MR.created_at, '%m') = '02'
group by title
order by avg(rating) DESC, title
limit 1

 

영화의 제목으로 내림차순 정렬한 후, 이제는 필요없어진 avg(rating)를 삭제한다. 그리고 가장 위에 있는 값만 반환하면 2월에 가장 높은 평균 평점을 받은 영화를 반환할 수 있다.

 

(select
    name as results
from
MovieRating MR
left join Users U on MR.user_id = U.user_id
group by name
order by count(name) DESC, name
limit 1)
union all
(select
    title
from
MovieRating MR
left join Movies M on MR.movie_id = M.movie_id
where date_format(MR.created_at, '%m') = '02'
group by title
order by avg(rating) DESC, title
limit 1)

 

이렇게 해서 테스트케이스를 통과할 수 있었지만 정답은 맞추지 못했다. 반례를 보면서 문제를 해결해보자.

 

(select
    name as results
from
MovieRating MR
left join Users U on MR.user_id = U.user_id
group by name
order by count(name) DESC, name
limit 1)
union all
(select
    title
from
MovieRating MR
left join Movies M on MR.movie_id = M.movie_id
where date_format(MR.created_at, '%Y%m') = '202002'
group by title
order by avg(rating) DESC, title
limit 1)

 

다시 살펴보니 문제는 간단했다. 02월의 평균평점을 구하는 것이 아니라 2020년 02월의 평균평점을 구해야 하는 문제였다. 예제에서 모든 년도가 2020년이라서 간과하고 말았다. 문제 조건에 맞게 평균평점이 높은 순서를 구하는 쿼리의 where조건을 수정해주고 다시 제출하면 성공!

완료