WITH cte AS (
SELECT mr.movie_id, mr.user_id, mr.rating, m.title, u.name, mr.created_at
FROM Users u
JOIN MovieRating mr ON u.user_id = mr.user_id
JOIN Movies m ON mr.movie_id = m.movie_id
)
SELECT results
FROM (
(
SELECT name AS results
FROM cte
GROUP BY user_id, name
HAVING COUNT() = (
SELECT MAX(cnt)
FROM (
SELECT COUNT() AS cnt
FROM cte
GROUP BY user_id
) AS subquery1
)
ORDER BY name ASC
LIMIT 1
)
UNION ALL
(
SELECT title AS results
FROM cte
WHERE YEAR(created_at) = 2020 AND MONTH(created_at) = 2
GROUP BY movie_id, title
HAVING AVG(rating) = (
SELECT MAX(rate) AS max_rate
FROM (
SELECT AVG(rating) AS rate
FROM cte
WHERE YEAR(created_at) = 2020 AND MONTH(created_at) = 2
GROUP BY movie_id
) AS subquery2
)
ORDER BY title ASC
LIMIT 1
)
) AS final_results; —- MovieRating =
| movie_id | user_id | rating | created_at |
| ——– | ——- | —— | ———- |
| 1 | 1 | 3 | 2020-02-16 |
| 1 | 2 | 5 | 2020-02-22 |
| 1 | 7 | 4 | 2020-01-26 |
| 1 | 13 | 1 | 2020-03-04 |
| 2 | 1 | 5 | 2020-01-29 |
| 2 | 3 | 5 | 2020-02-04 |
| 2 | 5 | 1 | 2020-02-03 |
| 2 | 6 | 1 | 2020-02-28 |
| 2 | 7 | 3 | 2020-02-12 |
| 2 | 8 | 2 | 2020-01-29 |
| 2 | 12 | 1 | 2020-02-24 |
| 3 | 2 | 2 | 2020-01-25 |
| 3 | 6 | 4 | 2020-02-27 |
| 3 | 7 | 1 | 2020-02-15 |
| 3 | 9 | 4 | 2020-02-28 |
| 3 | 14 | 3 | 2020-02-15 |
| 4 | 2 | 4 | 2020-01-28 |
| 4 | 4 | 2 | 2020-01-29 |
| 4 | 5 | 1 | 2020-01-26 |
| 4 | 6 | 2 | 2020-02-18 |
| 4 | 7 | 5 | 2020-02-23 |
| 4 | 8 | 4 | 2020-02-17 |
| 4 | 10 | 1 | 2020-01-29 |
| 4 | 11 | 1 | 2020-03-01 |
| 4 | 12 | 1 | 2020-01-26 |
| 5 | 3 | 1 | 2020-02-12 |
| 5 | 11 | 5 | 2020-01-30 |
| 5 | 14 | 1 | 2020-02-11 |
| 6 | 1 | 1 | 2020-02-07 |
| 6 | 2 | 1 | 2020-02-01 |
| 6 | 7 | 4 | 2020-02-08 |
| 6 | 11 | 2 | 2020-01-26 |
| 6 | 13 | 1 | 2020-02-12 |
| 7 | 4 | 4 | 2020-02-18 |
| 7 | 7 | 3 | 2020-01-29 |
| 7 | 8 | 1 | 2020-02-29 |
| 7 | 9 | 3 | 2020-01-26 |
| 7 | 12 | 1 | 2020-02-05 |
| 7 | 14 | 3 | 2020-01-30 |
| 8 | 2 | 4 | 2020-02-25 |
| 8 | 3 | 5 | 2020-03-02 |
| 8 | 5 | 5 | 2020-02-27 |
| 8 | 6 | 5 | 2020-02-08 |
| 8 | 10 | 4 | 2020-01-29 |
| 8 | 11 | 4 | 2020-01-30 |
| 9 | 4 | 5 | 2020-02-11 |
| 9 | 9 | 3 | 2020-02-26 |
| 9 | 11 | 5 | 2020-03-04 |
| 10 | 1 | 5 | 2020-01-26 |
| 10 | 7 | 5 | 2020-02-21 |
| 10 | 13 | 4 | 2020-02-17 |
| 11 | 2 | 3 | 2020-02-15 |
| 11 | 5 | 3 | 2020-03-02 |
| 11 | 11 | 3 | 2020-01-30 |
| 11 | 12 | 5 | 2020-02-06 |
| 12 | 1 | 4 | 2020-03-04 |
| 12 | 4 | 5 | 2020-02-10 |
| 12 | 5 | 3 | 2020-02-03 |
| 12 | 6 | 1 | 2020-01-26 |
| 12 | 11 | 5 | 2020-02-14 |
| movie_id | title |
|---|---|
| 1 | Five feets apart |
| 2 | Back to the Future |
| 3 | Shrek |
| 4 | Shazam |
| 5 | Mr. Glass |
| 6 | Joker |
| 7 | Avengers |
| 8 | Superman vs Batman |
| 9 | Frozen |
| 10 | Harry Potter |
| 11 | Ring |
| 12 | Lord of rings |
| user_id | name |
|---|---|
| 1 | Maria |
| 2 | Jade |
| 3 | Claire |
| 4 | Will |
| 5 | Anna |
| 6 | Daniel |
| 7 | Jonathan |
| 8 | Stella |
| 9 | Winston |
| 10 | Elvis |
| 11 | Timmy |
| 12 | Rose |
| 13 | Monica |
| 14 | Selena |
Output
| results |
|---|
| Jonathan |
Expected
| results |
| —————— |
| Jonathan |
| Superman vs Batman |
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.
What is wrong with my query?