1341 Movie Rating

Description

Table: Movies

• movie_id is the primary key (column with unique values) for this table.
• title is the name of the movie.

Table: Users

• user_id is the primary key (column with unique values) for this table.
• The column name has unique values.

Table: MovieRating

• (movie_id, user_id) is the primary key (column with unique values) 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.

Problem Statement

Write a solution 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 result format is in the following example.

Example 1:

Input:

• Movies table:

• Users table:

• MovieRating table:

Output:

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

The problem is asking for two separate questions.

1. Find the user name who has reviewed the highest number of movies.

For this, you first need to join Users table with MovieRating table.

1SELECT u.name, mr.rating AS num_ratings
2    FROM Users u
3    JOIN MovieRating mr ON u.user_id = mr.user_id;

Next, you need to find the user name who has reviewed most movies. This can be done using grouping operation. We need only one user name so we will also have to limit the results.

1SELECT u.name, COUNT(rating) AS num_ratings
2    FROM Users u
3    JOIN MovieRating mr ON u.user_id = mr.user_id
4    GROUP BY u.user_id ORDER BY num_ratings DESC, u.name 
5    LIMIT 1;

2. Find the movie name with highest average rating in February 2020.

In this case, you need to join Movies table with MovieRating table.

1SELECT m.title, mr.rating AS avg_ratings
2    FROM Movies m
3    JOIN MovieRating mr ON m.movie_id = mr.movie_id;

Then, you need to group by m.title and find the AVG(mr.rating). Notice that you also need to filter the ratings created in February 2020.

1SELECT m.title, AVG(mr.rating) AS avg_ratings
2    FROM Movies m
3    JOIN MovieRating mr ON m.movie_id = mr.movie_id
4    WHERE DATE_FORMAT(mr.created_at, '%Y-%m') = '2020-02'
5    GROUP BY m.title ORDER BY avg_ratings DESC, m.title
6    LIMIT 1;

The last part is to union these two results while renaming them. Below, I create two CTEs for above queries and union the results.

 1WITH best_reviewer AS (
 2    SELECT u.name, COUNT(rating) AS num_ratings
 3        FROM Users u
 4        JOIN MovieRating mr ON u.user_id = mr.user_id
 5        GROUP BY u.user_id ORDER BY num_ratings DESC, u.name 
 6        LIMIT 1
 7), best_movie AS (
 8    SELECT m.title, AVG(mr.rating) AS avg_ratings
 9        FROM Movies m
10        JOIN MovieRating mr ON m.movie_id = mr.movie_id
11        WHERE DATE_FORMAT(mr.created_at, '%Y-%m') = '2020-02'
12        GROUP BY m.title ORDER BY avg_ratings DESC, m.title
13        LIMIT 1
14) SELECT name AS results FROM best_reviewer
15UNION ALL
16SELECT title AS results FROM best_movie;