leetcode 1341 电影评分

leetcode 1341 电影评分

(
    select u1.name as results from Users u1 
    left join(
        select mr1.user_id, count(mr1.rating) as c1 from MovieRating as mr1
        group by mr1.user_id
        having c1 = (
            select max(p.c2) from Users as u2
            left join (
                select mr2.user_id, count(mr2.rating) as c2 from MovieRating as mr2
                group by mr2.user_id
            ) as p
            on u2.user_id = p.user_id
    )) p2
    on u1.user_id = p2.user_id
    where p2.user_id is not null
    order by u1.name asc
    limit 0,1
)
union all
(
    select m.title as results from Movies m
    left join (
        select mr4.movie_id, avg(mr4.rating) as d1 from MovieRating mr4 
        where EXTRACT(MONTH FROM mr4.created_at) = '02'
        and EXTRACT(YEAR FROM mr4.created_at) = '2020'
        group by mr4.movie_id 
        having round(d1,2) = (
            select round(max(p3.d2),2) from 
            (
                select mr3.movie_id, avg(mr3.rating) as d2 from MovieRating mr3 
                where EXTRACT(MONTH FROM mr3.created_at) = '02'
                and EXTRACT(YEAR FROM mr3.created_at) = '2020'
                group by mr3.movie_id 
            ) p3
        )
    ) p4
    on m.movie_id = p4.movie_id
    where p4.movie_id is not null
    order by m.title asc
    limit 0,1
)

 

==

 

(
    select u.name as results from Users u 
    left join(
        select mr1.user_id, count(mr1.rating) as umr from MovieRating as mr1
        group by mr1.user_id
    ) p1
    on u.user_id = p1.user_id
    where p1.user_id is not null
    order by p1.umr desc, u.name asc
    limit 0,1
)
union all
(
    select m.title as results from Movies m
    left join (
        select mr2.movie_id, avg(mr2.rating) as mmr from MovieRating mr2 
        where EXTRACT(MONTH FROM mr2.created_at) = '02'
        and EXTRACT(YEAR FROM mr2.created_at) = '2020'
        group by mr2.movie_id 
    ) p2
    on m.movie_id = p2.movie_id
    where p2.movie_id is not null
    order by p2.mmr desc, m.title asc
    limit 0,1
)

 

==