1341. Movie Rating
SQL 50 · Subqueries (day 6). Decode it with the hints, solve it on LeetCode, then compare with the reference solution in MySQLPostgreSQL.
Your work
Hints
Open one at a time. Try to finish with as few as you can.
1. What does one output row represent?
Two rows: one user name, one movie title.
2. Approach
Two queries, each with GROUP BY, ORDER BY count/avg DESC then name ASC, LIMIT 1, in parentheses, joined by UNION ALL.
3. The trap
UNION ALL, because a user name could equal a movie title. Movie average is February 2020 only.
Reference solution
Tested on MySQL 8.0 and PostgreSQL 16. Switch dialect at the top of the page.
Show the MySQLPostgreSQL solution
(SELECT u.name AS results
FROM MovieRating r
JOIN Users u ON r.user_id = u.user_id
GROUP BY u.user_id, u.name
ORDER BY COUNT(*) DESC, u.name
LIMIT 1)
UNION ALL
(SELECT m.title
FROM MovieRating r
JOIN Movies m ON r.movie_id = m.movie_id
WHERE r.created_at BETWEEN '2020-02-01' AND '2020-02-29'
GROUP BY m.movie_id, m.title
ORDER BY AVG(r.rating) DESC, m.title
LIMIT 1);