SQL Interview Questions – Movie Rating

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

Solution –

(
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
)