LeetCode SQL 50 ) 1341. 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.
Each movie has a unique title.
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. MovieRating에서 rating을 가장 많이 준 사람의 이름 찾기(여러명일 경우 사전순대로 젤 빠른거 하나)

2. MovieRating에서 2020년 2월 한달동안 평균 rating이 가장 높은 영화 찾기 (여러개일 경우 사전순대로 제일 빠른거 하나)

 

---

코드

우선 문제를 하나씩 해결해보자. 

1. MovieRating에서 rating을 가장 많이 준 사람의 이름 찾기(여러명일 경우 사전순대로 젤 빠른거 하나) 부터 만들어보자.

SELECT u.name as results
FROM Users u
JOIN MovieRating m on m.user_id = u.user_id
GROUP BY m.user_id
ORDER BY count(m.user_id) DESC, u.name
LIMIT 1

2. MovieRating에서 2020년 2월 한달동안 평균 rating이 가장 높은 영화 찾기 (여러개일 경우 사전순대로 제일 빠른거 하나)

SELECT m.title
FROM Movies m
JOIN MovieRating mr on m.movie_id = mr.movie_id
WHERE YEAR(mr.created_at) = 2020 AND MONTH(mr.created_at) = 2
GROUP BY mr.movie_id
ORDER BY avg(mr.rating) DESC, title  
LIMIT 1;

이제 1번과 2번을 합쳐 하나로 만들어준다. UNION ALL을 사용하면 된다.

(
SELECT u.name as results
FROM Users u
JOIN MovieRating m on m.user_id = u.user_id
GROUP BY m.user_id
ORDER BY count(m.user_id) DESC, u.name
LIMIT 1
)
UNION ALL
(
SELECT m.title
FROM Movies m
JOIN MovieRating mr on m.movie_id = mr.movie_id
WHERE YEAR(mr.created_at) = 2020 AND MONTH(mr.created_at) = 2
GROUP BY mr.movie_id
ORDER BY avg(mr.rating) DESC, title  
LIMIT 1
) ;

 

 

날짜 검색

날짜 검색에는 =, >, < 연산자나 BETWEEN, YEAR, MONTH, DAY, DATE_FORMAT을 이용할 수 있다.

이때 날짜는 ' ' 로 둘러쳐야 한다.

BETWEEN AND

SELECT * 
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';

지정한 날짜 사이의 데이터를 검색한다. 

>, = , <

SELECT * 
FROM orders
WHERE order_date >= '2023-01-01' AND order_date <= '2023-01-31';

< 는 특정 날짜 이전, > 는 특정 날짜 이후다. 

 

YEAR(), MONTH(), DAY()

특정 년, 월, 일에 해당하는 데이터를 검색한다.

SELECT * 
FROM orders
WHERE YEAR(order_date) = 2023 AND MONTH(order_date) = 1;

 

DATE_FORMAT()

날짜를 특정 형식으로 변환한 후 비교한다.

SELECT * 
FROM orders
WHERE DATE_FORMAT(order_date, '%Y-%m') = '2023-01';

 

 

UNION

UNION은 여러 쿼리문을 합쳐 하나의 쿼리문으로 만드는 방법이다. 두 종류가 있다.

• UNION : 중복된 값 제거하고 보여줌. 추가 연산 있기 때문에 UNION ALL 보다 느림
• UNION ALL: 중복된 값 모두 보여줌

UNION을 사용하려면 컬럼명이 동일해야 하고, 컬럼별로 데이터 타입이 동일해야 하며, 출력할 컬럼의 개수가 동일해야 한다.

JOIN은 새로운 열로 결합하고(수평결합) UNION은 새로운 행으로 결합한다. (수직결합)