Problem Description –
Write an SQL query 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 query result format is in the following example.
Difficulty Level – Medium
Problem Link – Movie Rating
( SELECT u.name as results FROM MovieRating as m JOIN Users as u ON m.user_id = u.user_id GROUP BY 1 ORDER BY COUNT(*) DESC, 1 LIMIT 1 ) UNION ( SELECT m.title FROM MovieRating as mr JOIN Movies as m ON mr.movie_id = m.movie_id WHERE DATE_FORMAT(created_at, '%Y-%m') = '2020-02' GROUP BY 1 ORDER BY AVG(rating) DESC, 1 LIMIT 1 )