SQL문제풀기

[SQL 문제 풀이] Movie Rating (영화 등급)

Jul 03, 2025

[SQL 문제 풀이] Movie Rating (영화 등급)

Contents

내가 작성한 쿼리

Movie Rating - LeetCode

Can you solve this real interview question? Movie Rating - Table: Movies +---------------+---------+ | Column Name | Type | +---------------+---------+ | movie_id | int | | title | varchar | +---------------+---------+ movie_id is the primary key (column with unique values) for this table. title is the name of the movie. Table: Users +---------------+---------+ | Column Name | Type | +---------------+---------+ | user_id | int | | name | varchar | +---------------+---------+ user_id is the primary key (column with unique values) for this table. The column 'name' has unique values. Table: MovieRating +---------------+---------+ | Column Name | Type | +---------------+---------+ | movie_id | int | | user_id | int | | rating | int | | created_at | date | +---------------+---------+ (movie_id, user_id) is the primary key (column with unique values) for this table. This table contains the rating of a movie by a user in their review. created_at is the user's review date. Write a solution to: * Find the name of the user who has rated the greatest number of movies. In case of a tie, return the lexicographically smaller user name. * Find the movie name with the highest average rating in February 2020. In case of a tie, return the lexicographically smaller movie name. The result format is in the following example. Example 1: Input: Movies table: +-------------+--------------+ | movie_id | title | +-------------+--------------+ | 1 | Avengers | | 2 | Frozen 2 | | 3 | Joker | +-------------+--------------+ Users table: +-------------+--------------+ | user_id | name | +-------------+--------------+ | 1 | Daniel | | 2 | Monica | | 3 | Maria | | 4 | James | +-------------+--------------+ MovieRating table: +-------------+--------------+--------------+-------------+ | movie_id | user_id | rating | created_at | +-------------+--------------+--------------+-------------+ | 1 | 1 | 3 | 2020-01-12 | | 1 | 2 | 4 | 2020-02-11 | | 1 | 3 | 2 | 2020-02-12 | | 1 | 4 | 1 | 2020-01-01 | | 2 | 1 | 5 | 2020-02-17 | | 2 | 2 | 2 | 2020-02-01 | | 2 | 3 | 2 | 2020-03-01 | | 3 | 1 | 3 | 2020-02-22 | | 3 | 2 | 4 | 2020-02-25 | +-------------+--------------+--------------+-------------+ Output: +--------------+ | results | +--------------+ | Daniel | | Frozen 2 | +--------------+ Explanation: Daniel and Monica have rated 3 movies ("Avengers", "Frozen 2" and "Joker") but Daniel is smaller lexicographically. Frozen 2 and Joker have a rating average of 3.5 in February but Frozen 2 is smaller lexicographically.

Movie Rating - LeetCode

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

Movie Rating - LeetCode

notion image

notion image


해결책을 작성하세요:

* 가장 많은 영화를 평점한 사용자의 이름을 찾습니다.
	동점일 경우 사전적으로 작은 사용자 이름을 반환합니다.
* 2020년 2월에 평균 평점이 가장 높은 영화 이름을 찾습니다.
	동점일 경우 사전적으로 작은 영화 이름을 반환합니다.

내가 작성한 쿼리

MySQL


-- 가장 많은 영화를 평가한 사용자
SELECT name AS results
FROM (
    SELECT
        u.name,
        COUNT(*) AS rating_count
    FROM MovieRating mr
    JOIN Users u 
        ON mr.user_id = u.user_id
    GROUP BY mr.user_id, u.name
    ORDER BY rating_count DESC, u.name ASC
    LIMIT 1
) AS TopUser

UNION ALL

-- 2020년 2월 평균 평점이 가장 높은 영화
SELECT title AS results
FROM (
    SELECT 
        m.title,
        AVG(mr.rating) AS avg_rating
    FROM MovieRating mr
    JOIN Movies m 
        ON mr.movie_id = m.movie_id
    WHERE 
        mr.created_at >= '2020-02-01' 
        AND mr.created_at < '2020-03-01'
    GROUP BY m.movie_id, m.title
    ORDER BY avg_rating DESC, m.title ASC
    LIMIT 1
) AS TopMovie;

Oracle


-- 가장 많은 영화를 평가한 사용자
SELECT name AS results
FROM (
    SELECT u.name,
           COUNT(*) AS rating_count,
           ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC, u.name ASC) AS rn
    FROM MovieRating mr
    JOIN Users u 
        ON mr.user_id = u.user_id
    GROUP BY mr.user_id, u.name
)
WHERE rn = 1

UNION ALL

-- 2020년 2월 평균 평점이 가장 높은 영화
SELECT title AS results
FROM (
    SELECT m.title,
           AVG(mr.rating) AS avg_rating,
           ROW_NUMBER() OVER (ORDER BY AVG(mr.rating) DESC, m.title ASC) AS rn
    FROM MovieRating mr
    JOIN Movies m
        ON mr.movie_id = m.movie_id
    WHERE mr.created_at >= DATE '2020-02-01'
        AND mr.created_at < DATE '2020-03-01'
    GROUP BY m.movie_id, m.title
)
WHERE rn = 1;

차이점

항목	MySQL	Oracle
최상위 N개 추출	`LIMIT 1` 사용	`ROW_NUMBER()` + `WHERE rn = 1` 또는 `FETCH FIRST 1`
날짜 상수 리터럴	`'2020-02-01'` (문자열로 처리)	`DATE '2020-02-01'` (명시적 날짜 리터럴 사용)

Share article

More articles

[JAVA 문제 풀이] 361. 연속된 부분 수열의 합

[JAVA 문제 풀이] 361. 연속된 부분 수열의 합

[SQL 문제 풀이] Find Drivers with Improved Fuel Efficiency (연비가 향상된 드라이버 찾기)

[SQL 문제 풀이] Find Drivers with Improved Fuel Efficiency (연비가 향상된 드라이버 찾기)

[JAVA 문제 풀이] 360. Find the K-th Character in String Game I [스트링 게임 I에서 K번째 문자 찾기]

[JAVA 문제 풀이] 360. Find the K-th Character in String Game I [스트링 게임 I에서 K번째 문자 찾기]

[JAVA 문제 풀이] 359. 큰 수 만들기

[JAVA 문제 풀이] 359. 큰 수 만들기

stupefyee

RSS·Powered by Inblog