Movie Rating
Task: Find the name of the user who has rated the greatest number of movies. Find the movie name with the highest average rating in February 2020. Combine using UNION ALL.
Table: Movies
+-------------+-------------+
| Column Name | Type |
+-------------+-------------+
| movie_id | INT |
| title | varchar(30) |
+-------------+-------------+Table: Users
+-------------+-------------+
| Column Name | Type |
+-------------+-------------+
| user_id | INT |
| name | varchar(30) |
+-------------+-------------+Table: MovieRating
+-------------+------+
| Column Name | Type |
+-------------+------+
| movie_id | INT |
| user_id | INT |
| rating | INT |
| created_at | date |
+-------------+------+Input: Movies table:
+----------+----------+
| movie_id | title |
+----------+----------+
| 1 | Avengers |
| 2 | Frozen 2 |
| 3 | Joker |
+----------+----------+Users table:
+---------+--------+
| user_id | name |
+---------+--------+
| 1 | Daniel |
| 2 | Monica |
| 3 | Maria |
| 4 | James |
+---------+--------+MovieRating table:
+----------+---------+--------+------------+
| movie_id | user_id | rating | created_at |
+----------+---------+--------+------------+
| 1 | 1 | 3 | 2020-01-12 |
| 1 | 2 | 4 | 2020-02-11 |
| 1 | 3 | 2 | 2020-02-12 |
| 1 | 4 | 1 | 2020-01-01 |
| 2 | 1 | 5 | 2020-02-17 |
| 2 | 2 | 2 | 2020-02-01 |
| 2 | 3 | 2 | 2020-03-01 |
| 3 | 1 | 3 | 2020-02-22 |
| 3 | 2 | 4 | 2020-02-25 |
+----------+---------+--------+------------+Output:
+----------+
| results |
+----------+
| Daniel |
| Frozen 2 |
+----------+View Reference Solution & Explanation ▼
This solution uses a `JOIN` to combine data from multiple tables, the `GROUP BY` clause to aggregate results, `UNION` to combine the result sets of multiple queries, a subquery as a derived table or inline value, date functions to handle time-based filtering or grouping, and `LIMIT 1` to return only the top record after sorting.
SELECT name AS results FROM (SELECT u.name FROM Users u JOIN MovieRating mr ON u.user_id = mr.user_id GROUP BY u.user_id ORDER BY COUNT(*) DESC, u.name ASC LIMIT 1) UNION ALL SELECT title AS results FROM (SELECT m.title FROM Movies m JOIN MovieRating mr ON m.movie_id = mr.movie_id WHERE STRFTIME('%Y-%m', mr.created_at) = '2020-02' GROUP BY m.movie_id ORDER BY AVG(mr.rating) DESC, m.title ASC LIMIT 1);