1341. Movie Rating

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

 

 

 

포트폴리오 쓴다고, 그동안 쿼리연습을 못했다.

이전 기억 상기시키기에 좋은 쉬운문제

 

 

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. 가장 많은 수의 영화에 평점 매긴 유저 이름 반환, 동률일경우 알파벳순으르 빠른사람 상위 1명
-- 2. 2020년 2월, 평균평점이 가장 높은 영화이름 동률일경우 알파벳순으르 빠른영화 상위 1개
-- 3. 위/아래 테이블 병합



WITH CTE AS(
                SELECT MovieRating.rating
                      ,MovieRating.created_at
                      ,Users.name
                      ,Movies.title
                
                FROM MovieRating
                     INNER JOIN Movies ON MovieRating.movie_id=Movies.movie_id
                     INNER JOIN Users ON MovieRating.user_id=Users.user_id
            )

(SELECT name AS results
FROM CTE
GROUP BY name
ORDER BY count(name) DESC, name
LIMIT 1)


UNION ALL


(SELECT title AS results
FROM CTE
WHERE DATE_FORMAT(created_at, "%Y-%m") = "2020-02"
GROUP BY title
ORDER BY AVG(rating) DESC, title
LIMIT 1)