Movie Rating SQL MySQL Leetcode solution with UNION ALL and DATEFORMAT
21 بار بازدید -
2 ماه پیش
-
Challenge: Analyzing Movie RatingsDifficulty: MediumData:There
Challenge: Analyzing Movie Ratings
Difficulty: Medium
Data:
There are three tables involved:
Movies: Stores movie information (movie_id, title).
Users: Stores user information (user_id, name).
MovieRating: Stores movie ratings by users (movie_id, user_id, rating, created_at). (movie_id, user_id) is the primary key for this table.
Objective:
Most Prolific User: Find the user who has rated the greatest number of movies. In case of a tie, return the user with the lexicographically smaller name.
Top-Rated Movie (February 2020): Find the movie with the highest average rating in February 2020. In case of a tie (same average rating), return the movie with the lexicographically smaller title.
SQL MySQL Leetcode solution with union all.
The provided MySQL query consists of two parts, each part executing a SELECT statement and then combining their results using the UNION ALL operator. Let's break down each part of the query to understand its functionality and purpose.
FROM MovieRating INNER JOIN Users USING (user_id):
This joins the MovieRating table with the Users table based on the user_id column. This means each row in the resulting table will contain data from both MovieRating and Users for matching user_ids.
GROUP BY user_id, name:
Groups the results by each user (user_id) and their name (name). This ensures that the count is calculated per user.
ORDER BY COUNT(MovieRating.movie_id) DESC, Users.name:
Orders the grouped results first by the number of movies rated by each user (COUNT(MovieRating.movie_id)) in descending order. If there is a tie (i.e., multiple users have rated the same number of movies), it further orders them alphabetically by Users.name.
LIMIT 1:
Limits the result to only one row. Therefore, this part of the query retrieves the name of the user who has rated the most movies. In case of a tie, the user whose name is alphabetically first is selected.
SELECT Users.name AS results:
Selects the name of the user as results.
Second Part of the Query
FROM MovieRating INNER JOIN Movies USING (movie_id):
This joins the MovieRating table with the Movies table based on the movie_id column. This means each row in the resulting table will contain data from both MovieRating and Movies for matching movie_ids.
WHERE DATE_FORMAT(created_at, '%Y-%m') = '2020-02':
Filters the results to include only those movie ratings that were created in February 2020.
GROUP BY movie_id, title:
Groups the results by each movie (movie_id) and its title (title). This ensures that the average rating is calculated per movie.
ORDER BY AVG(MovieRating.rating) DESC, Movies.title:
Orders the grouped results first by the average rating of the movies (AVG(MovieRating.rating)) in descending order. If there is a tie (i.e., multiple movies have the same average rating), it further orders them alphabetically by Movies.title.
LIMIT 1:
Limits the result to only one row. Therefore, this part of the query retrieves the title of the highest-rated movie in February 2020. In case of a tie, the movie whose title is alphabetically first is selected.
SELECT Movies.title AS results:
Selects the title of the movie as results.
Combined Results
The UNION ALL operator combines the results of the two parts into a single result set.
The first row of the combined result set contains the name of the user who has rated the most movies.
The second row contains the title of the highest-rated movie in February 2020.
By understanding each part separately, we can see that the entire query is designed to fetch the top user based on the number of movies rated and the top movie based on ratings in a specific month, and then combine these results into a single output.
Difficulty: Medium
Data:
There are three tables involved:
Movies: Stores movie information (movie_id, title).
Users: Stores user information (user_id, name).
MovieRating: Stores movie ratings by users (movie_id, user_id, rating, created_at). (movie_id, user_id) is the primary key for this table.
Objective:
Most Prolific User: Find the user who has rated the greatest number of movies. In case of a tie, return the user with the lexicographically smaller name.
Top-Rated Movie (February 2020): Find the movie with the highest average rating in February 2020. In case of a tie (same average rating), return the movie with the lexicographically smaller title.
SQL MySQL Leetcode solution with union all.
The provided MySQL query consists of two parts, each part executing a SELECT statement and then combining their results using the UNION ALL operator. Let's break down each part of the query to understand its functionality and purpose.
FROM MovieRating INNER JOIN Users USING (user_id):
This joins the MovieRating table with the Users table based on the user_id column. This means each row in the resulting table will contain data from both MovieRating and Users for matching user_ids.
GROUP BY user_id, name:
Groups the results by each user (user_id) and their name (name). This ensures that the count is calculated per user.
ORDER BY COUNT(MovieRating.movie_id) DESC, Users.name:
Orders the grouped results first by the number of movies rated by each user (COUNT(MovieRating.movie_id)) in descending order. If there is a tie (i.e., multiple users have rated the same number of movies), it further orders them alphabetically by Users.name.
LIMIT 1:
Limits the result to only one row. Therefore, this part of the query retrieves the name of the user who has rated the most movies. In case of a tie, the user whose name is alphabetically first is selected.
SELECT Users.name AS results:
Selects the name of the user as results.
Second Part of the Query
FROM MovieRating INNER JOIN Movies USING (movie_id):
This joins the MovieRating table with the Movies table based on the movie_id column. This means each row in the resulting table will contain data from both MovieRating and Movies for matching movie_ids.
WHERE DATE_FORMAT(created_at, '%Y-%m') = '2020-02':
Filters the results to include only those movie ratings that were created in February 2020.
GROUP BY movie_id, title:
Groups the results by each movie (movie_id) and its title (title). This ensures that the average rating is calculated per movie.
ORDER BY AVG(MovieRating.rating) DESC, Movies.title:
Orders the grouped results first by the average rating of the movies (AVG(MovieRating.rating)) in descending order. If there is a tie (i.e., multiple movies have the same average rating), it further orders them alphabetically by Movies.title.
LIMIT 1:
Limits the result to only one row. Therefore, this part of the query retrieves the title of the highest-rated movie in February 2020. In case of a tie, the movie whose title is alphabetically first is selected.
SELECT Movies.title AS results:
Selects the title of the movie as results.
Combined Results
The UNION ALL operator combines the results of the two parts into a single result set.
The first row of the combined result set contains the name of the user who has rated the most movies.
The second row contains the title of the highest-rated movie in February 2020.
By understanding each part separately, we can see that the entire query is designed to fetch the top user based on the number of movies rated and the top movie based on ratings in a specific month, and then combine these results into a single output.
2 ماه پیش
در تاریخ 1403/03/06 منتشر شده
است.
21
بـار بازدید شده