[LeetCode] 1341. Movie Rating

문제를 잘 읽자!!! subquery를 잘 활용하자..!

 

Question

 

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.

The result format is in the following example.

 

Code

 

select name as results
from users
where user_id = (
    select user_id
    from movierating
    join users using(user_id)
    group by 1
    order by count(*) desc, name
    limit 1
)

union all

select title as results
from movies
where movie_id = (
    select movie_id
    from movierating
    join movies using(movie_id)
    where created_at like '2020-02%'
    group by 1
    order by avg(rating) desc, title
    limit 1
)

 

My Code