[코드카타] SQL 54 Movie Rating

[코드카타] SQL 54 Movie Rating

54 Movie Rating
https://leetcode.com/problems/movie-rating/solutions/

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.

with temp_movie as (
    select mr.movie_id, user_id, title, avg(rating) max_rate
    from MovieRating mr
    join Movies m on mr.movie_id=m.movie_id
    where date_format(created_at,'%Y-%m') = '2020-02'
    group by mr.movie_id
    order by max_rate desc, title limit 1
), temp_user as (
    select movie_id, mr.user_id, name, count(*) cnt_rate
    from MovieRating mr
    join Users u on mr.user_id=u.user_id
    group by mr.user_id
    order by cnt_rate desc, name limit 1
)
select name as results
from temp_user
union all
select title
from temp_movie

조건 1과 조건 2에 대해서 각각 찾은 후 union all로 수직 결합 실시!