SQL | LeetCode 1341. 집계(Aggregation), 날짜 필터링, 정렬 및 서브쿼리 활용

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.

1. 가장 많은 영화를 평가한 사용자의 이름을 찾기. (동점일 경우 이름 기준 오름차순)
2. 2020년 2월에 평균 평점이 가장 높은 영화의 제목을 찾기. (동점일 경우 제목 기준 오름차순)

(select name as results from users u join movierating mr on u.user_id = mr.user_id
group by u.name
order by count(*) desc, u.name
limit 1)

union all
(select title as results from movies m join movierating mr on m.movie_id = mr.movie_id
WHERE created_at BETWEEN '2020-02-01' AND '2020-02-29'
group by m.title
order by avg(rating) desc, m.title
limit 1)

1) 가장 많이 평가한 사용자

• 사용자별로 평가 횟수를 세고,
• 가장 많은 평가를 한 사용자 찾기
• 동점일 경우 name 기준 오름차순 정렬

2) 2020년 2월 평균 평점이 가장 높은 영화

• created_at이 2020년 2월인 평가만 필터링
• 영화별 평균 평점을 구하고,
• 최고 평점 영화 찾기 (동점이면 제목 순)