문제
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.
두 문제이다. 하나는 가장 영화 리뷰를 많이 남긴 사람의 이름을 출력해야한다. 만약 리뷰 수가 같다면 사전순으로 더 작은 사람을 출력한다.
다른 하나는 2020년 2월에 평균 평점이 가장 높은 영화 이름을 찾야야한다. 동점인 경우에 사전순으로 더 작은 영화 이름을 출력한다.
테이블 및 예제
[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.
[Users]
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user_id | int |
| name | varchar |
+---------------+---------+
user_id is the primary key (column with unique values) for this 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.
[Examples]
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 |
+--------------+
풀이
임시테이블을 사용해서 조건에 맞는 테이블을 생성하고 limit을 걸어서 열을 뽑아낸다. 후에 UNION을 사용해서 합쳐주면 결과를 도출 가능하다.
WITH
g_movie AS (
SELECT r.movie_id,COUNT(r.user_id) AS cnt, m.title, u.name
FROM MovieRating AS r
JOIN Movies AS m ON r.movie_id = m.movie_id JOIN Users AS u ON u.user_id = r.user_id
GROUP BY r.user_id
),
fin1 AS (
SELECT name AS results
FROM g_movie
ORDER BY cnt DESC, name
limit 1
),
FE20 AS(
SELECT m.title AS results
FROM MovieRating AS mr JOIN Movies AS m
ON mr.movie_id = m.movie_id
WHERE mr.created_at BETWEEN '2020-02-01' AND '2020-02-28'
GROUP BY mr.movie_id
ORDER BY AVG(mr.rating) DESC, m.title
limit 1
)
SELECT * FROM FE20
UNION ALL
SELECT * FROM fin1
처음에는 각각 임시테이블을 만들고 조건에 맞는 열을 뽑아냈는데 UNION을 하는게 어려워졌다.
따라서 위와 같은 방식으로 열을 뽑아내는 임시테이블을 쭉 만들어낸 다음에 테이블을 가져와서 UNION 해주었다.
'코딩테스트 > SQL' 카테고리의 다른 글
| SQL > LeetCode 1321. Restaurant Growth (MySQL) (0) | 2024.06.17 |
|---|---|
| SQL > WINDOW FUNCTION / ROWS & RANGE (0) | 2024.06.17 |
| SQL > LeetCode 1907. Count Salary Categories (MySQL) (2) | 2024.06.13 |
| SQL > LeetCode Problem Solving: Last Person to Fit in the Bus (AI.ver) (0) | 2024.06.12 |
| SQL > LeetCode 1204. Last Person to Fit in the Bus (MySQL) (0) | 2024.06.12 |