# $1341. 电影评分
# https://leetcode-cn.com/problems/movie-rating/

# SQL架构
Create table If Not Exists Movies (movie_id int, title varchar(30));
Create table If Not Exists Users (user_id int, name varchar(30));
Create table If Not Exists MovieRating (movie_id int, user_id int, rating int, created_at date);
Truncate table Movies;
insert into Movies (movie_id, title) values ('1', 'Avengers');
insert into Movies (movie_id, title) values ('2', 'Frozen 2');
insert into Movies (movie_id, title) values ('3', 'Joker');
Truncate table Users;
insert into Users (user_id, name) values ('1', 'Daniel');
insert into Users (user_id, name) values ('2', 'Monica');
insert into Users (user_id, name) values ('3', 'Maria');
insert into Users (user_id, name) values ('4', 'James');
Truncate table MovieRating;
insert into MovieRating (movie_id, user_id, rating, created_at) values ('1', '1', '3', '2020-01-12');
insert into MovieRating (movie_id, user_id, rating, created_at) values ('1', '2', '4', '2020-02-11');
insert into MovieRating (movie_id, user_id, rating, created_at) values ('1', '3', '2', '2020-02-12');
insert into MovieRating (movie_id, user_id, rating, created_at) values ('1', '4', '1', '2020-01-01');
insert into MovieRating (movie_id, user_id, rating, created_at) values ('2', '1', '5', '2020-02-17');
insert into MovieRating (movie_id, user_id, rating, created_at) values ('2', '2', '2', '2020-02-01');
insert into MovieRating (movie_id, user_id, rating, created_at) values ('2', '3', '2', '2020-03-01');
insert into MovieRating (movie_id, user_id, rating, created_at) values ('3', '1', '3', '2020-02-22');
insert into MovieRating (movie_id, user_id, rating, created_at) values ('3', '2', '4', '2020-02-25');

# Write your MySQL query statement below
select
    results
from
    (
        select
            name as results
        from
            Users as u
            inner join MovieRating mr on u.user_id = mr.user_id
        group by
            u.user_id,
            # sql_mode=only_full_group_by
            name
        order by
            count(*) desc,
            name
        limit
            1
    ) tmp1
union
select
    results
from
    (
        select
            title as results
        from
            Movies m
            inner join MovieRating mr on m.movie_id = mr.movie_id
        where
            left(created_at, 7) = '2020-02'
        group by
            m.movie_id,
            # sql_mode=only_full_group_by
            title
        order by
            avg(rating) desc,
            title
        limit
            1
    ) tmp2;

# clean-up
drop table Movies;
drop table Users;
drop table MovieRating;