[SQL문제풀이-Groupby/Subquery] Movie Rating

2025.02.24 - [프로그래밍/SQL, Hive, SAS 관련 정보] - [SQL] Table of Contents

---

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

 

제가 시도했던 방법은 아래와 같습니다. 일단 MovieRating table에서 user_id별로 grouping을 시키고 join을 해야한다고 판단했습니다. 그런데 생각해보면 반드시 그럴필요는 없긴합니다. 조인 후에 해도 되는 것이죠. 

WITH temp AS (
    SELECT user_id, COUNT(movie_id) AS cnt
    FROM MovieRating
    GROUP BY user_id
),
temp2 AS (
    SELECT movie_id, AVG(rating) AS rate
    FROM MovieRating
    WHERE YEAR(created_at) = 2020 AND MONTH(created_at) = 2
    GROUP BY movie_id
)

-- 가장 많은 영화를 평가한 사용자 (사전순 1명)
SELECT *
FROM (
    SELECT u.name AS results
    FROM temp t
    JOIN Users u ON t.user_id = u.user_id
    WHERE t.cnt = (SELECT MAX(cnt) FROM temp)
    ORDER BY u.name ASC
    LIMIT 1
) AS top_user

UNION all

-- 2020년 2월에 가장 높은 평점을 받은 영화 (사전순 1개)
SELECT *
FROM (
    SELECT m.title AS results
    FROM temp2 t
    JOIN Movies m ON t.movie_id = m.movie_id
    WHERE t.rate = (
        SELECT MAX(rate)
        FROM temp2
    )
    ORDER BY m.title ASC
    LIMIT 1
) AS top_movie;

 

또 제 쿼리는 최대 빈도수, 최고 평균평점에 대응되는 row를 뽑아낼 때 sub-query로 처리했는데 좀 더 간단한 방법이 있었죠. 그룹별로 집계(count, avg)해서 그중 최대(max)값을 뽑아내는 것이니.. group by 함수에서 곧바로 뽑아낼 수 있었습니다. 제가 헷갈렸던 것은 '최빈값 찾아내기' 포스팅에서의 상황이 아닌가 했던 것인데요.

2025.01.30 - [프로그래밍/SQL, Hive, SAS 관련 정보] - [SQL 기초] 최빈값 찾아내기

최빈값 찾아내기에서는 (SQL 문법 상) 집계되기 이전의 Raw 값중에서 그룹별로 특정한 조건을 만족하는 값을 추출해내는 것이라고 봐야겠죠. 

 

그리고 제가 스트레스(?!) 받는 날짜의 처리도 year, month를 각각 뽑아내서 완벽하게 일치하는 녀석을 찾아내는 방법도 있지만 다양한 방법이 존재합니다. 

WHERE created_at LIKE '2020-02%'
WHERE EXTRACT(YEAR_MONTH FROM created_at) = 202002
WHERE DATE_FORMAT(created_at,'%Y-%m') = '2020-02'
WHERE created_at BETWEEN '2020-02-01' AND '2020-02-29'

 

 

이를 종합하면..좀 더 간단한 코드는 아래와 같습니다. 

(
    SELECT u.name AS results
    FROM users u
    LEFT JOIN movierating m ON u.user_id = m.user_id
    GROUP BY u.user_id
    ORDER BY COUNT(m.rating) DESC, u.name ASC
    LIMIT 1
)

UNION ALL

-- Get the movie with the highest average rating in Feb 2020
(
    SELECT mo.title AS results
    FROM movies mo
    LEFT JOIN movierating mr ON mo.movie_id = mr.movie_id
    WHERE mr.created_at LIKE '2020-02%'
    GROUP BY mo.movie_id
    ORDER BY AVG(mr.rating) DESC, mo.title ASC
    LIMIT 1
);