[LeetCode-SQL 50] 1341. Movie Rating

문제

https://leetcode.com/problems/movie-rating/description/?envType=study-plan-v2&envId=top-sql-50

설명

두 개의 테이블을 조인하여, 가장 많은 수의 영화를 평가한 사용자와 평균 평점이 가장 높은 영화를 찾는 문제이다.

UNION ALL과 집계함수(sum,avg), 윈도우 함수(rank), 서브쿼리 등 신경쓸 게 많았던 문제였다.

• 날짜 비교는 문자열로 감싸진 'YYYY-MM-DD' 형식의 값을 사용
• 내림차순은 DESC
• 서브쿼리에 반드시 별칭(ALIAS) 추가
• CASE문에 조건이 1개인 경우에는 WHERE 사용
• group by 문에 id, name을 모두 포함하여 id로 고유한 데이터를 기준으로 그룹핑하고 name을 컬럼으로 사용
• 윈도우 함수 포맷: METHOD() OVER (ORDER BY 컬럼 DESC) AS 별칭
• WITH 테이블명 AS (테이블) 을 사용하여, SQL문을 깔끔하게 만들 수 있다.
• 현재 코드에서는 제일 큰 수만 구하는 것이므로, 굳이 Rank를 쓰지 않고, min 집계 함수를 사용하면 더 간단히 사용할 수 있다.

구현

 

with top_user as (
    SELECT u.name AS name, COUNT(*) AS rating_num, 
           RANK() OVER (ORDER BY COUNT(*) DESC) AS num_rank
    FROM Users u
    JOIN MovieRating mr ON u.user_id = mr.user_id
    GROUP BY u.user_id
), top_movie as (
    SELECT RANK() OVER (ORDER BY avg_rating DESC) AS avg_rating_rank, title
    FROM (
        SELECT m.title AS title, AVG(mr.rating) AS avg_rating
        FROM Movies m 
        JOIN MovieRating mr ON m.movie_id = mr.movie_id
        WHERE mr.created_at >= '2020-02-01' AND mr.created_at < '2020-03-01'
        GROUP BY m.movie_id) t
)

( SELECT name AS results
FROM top_user
WHERE num_rank = 1
ORDER BY name
LIMIT 1 )

UNION ALL 

( SELECT title AS results
FROM top_movie tm
WHERE avg_rating_rank = 1
# where avg_rating_rank = (select min(avg_rating_rank) from tm)
ORDER BY title 
LIMIT 1 );