LeetCode 1341.电影评分

数据准备

drop table Users;

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');

需求

请你编写一组 SQL 查询：

查找评论电影数量最多的用户名。如果出现平局，返回字典序较小的用户名。
查找在 February 2020 平均评分最高 的电影名称。如果出现平局，返回字典序较小的电影名称。

输入

输出

1. 查找评论电影数量最多的用户名。如果出现平局，返回字典序较小的用户名。

-- 查找评论电影数量最多的用户名。如果出现平局，返回字典序较小的用户名。
select name,
       count(1) as cnt_name
from Users u ,MovieRating mr
where u.user_id=mr.user_id
group by name
order by cnt_name desc ,name
limit 1;

2. 查找在 2020年2月 平均评分最高 的电影名称。如果出现平局，返回字典序较小的电影名称。

-- 查找在  2020年2月 平均评分最高 的电影名称。如果出现平局，返回字典序较小的电影名称。
with t1 as (
    select mr.*,m.title
    from MovieRating mr,Movies m
    where mr.movie_id=m.movie_id and substr(created_at,1,7)='2020-02'
)
select title,
#        sum(rating) as rat_sum,
#        count(1) as cnt,
       round(sum(rating) / count(1) , 2) as avg_rat
from t1
group by title
order by avg_rat desc,title
limit 1
;

3. 将查询结果拼接union all

-- 将查询结果拼接union all
with t1 as (
    select mr.*,m.title
    from MovieRating mr,Movies m
    where mr.movie_id=m.movie_id and substr(created_at,1,7)='2020-02'
),t2 as (
    select title,
#        sum(rating) as rat_sum,
#        count(1) as cnt,
       round(sum(rating) / count(1) , 2) as avg_rat
    from t1
    group by title
    order by avg_rat desc,title
    limit 1
),t3 as (
    select name,
       count(1) as cnt_name
    from Users u ,MovieRating mr
    where u.user_id=mr.user_id
    group by name
    order by cnt_name desc ,name
    limit 1
)
select name as results from t3
union all
select title as results from t2;