LeetCode-in-Java

1341. Movie Rating

Medium

SQL Schema

Table: Movies

+---------------+---------+ 
| Column Name   | Type    | 
+---------------+---------+ 
| movie_id      | int     | 
| title         | varchar | 
+---------------+---------+ 

movie_id is the primary key for this table. title is the name of the movie.

Table: Users

+---------------+---------+ 
| Column Name   | Type    | 
+---------------+---------+ 
| user_id       | int     | 
| name          | varchar | 
+---------------+---------+ 

user_id is the primary key for this table.

Table: MovieRating

+---------------+---------+ 
| Column Name   | Type    | 
+---------------+---------+ 
| movie_id      | int     | 
| user_id       | int     | 
| rating        | int     | 
| created_at    | date    | 
+---------------+---------+ 

(movie_id, user_id) is the primary key for this table. This table contains the rating of a movie by a user in their review. created_at is the user’s review date.

Write an SQL query to:

The query result format is in the following example.

Example 1:

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     | 
+--------------+

Explanation:

Daniel and Monica have rated 3 movies (“Avengers”, “Frozen 2” and “Joker”) but Daniel is smaller lexicographically.

Frozen 2 and Joker have a rating average of 3.5 in February but Frozen 2 is smaller lexicographically.

Solution

# Write your MySQL query statement below
(SELECT name results
FROM Users as U, MovieRating as  MR
WHERE U.user_id = MR.user_id
GROUP BY U.user_id
ORDER BY COUNT(MR.user_id) DESC, name ASC LIMIT 1)
UNION ALL
(SELECT title results
FROM Movies as M, MovieRating as MR
WHERE M.movie_id = MR.movie_id AND created_at BETWEEN '2020-02-01' AND '2020-02-29'
GROUP BY M.movie_id
ORDER BY AVG(rating) DESC, title ASC LIMIT 1)