LeetCode - 1341. Movie Rating (MySQL)

Medium, SQL - UNION ALL

RunTime : 1094 ms

문제

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.

풀이

• 두 쿼리를 작성해 UNION으로 묶어야 한다는 생각은 했는데.
  예시 중에 사람 이름이랑 영화 제목이 겹치는게 있었다.
• UNION ALL은 중복행을 같이 보여주기 때문에 UNION ALL 사용

(SELECT U.name as results FROM Users as U
JOIN MovieRating as M ON U.user_id = M.user_id
GROUP BY U.name
ORDER BY COUNT(*) DESC, U.name
LIMIT 1)

UNION ALL

(SELECT M.title as results FROM Movies as M
JOIN MovieRating as MR ON M.movie_id = MR.movie_id
WHERE MR.created_at LIKE '2020-02%'
GROUP BY M.title
ORDER BY AVG(MR.rating) DESC, M.title
LIMIT 1)