Netflix Genre Ratings
Netflix is a popular streaming platform that offers a wide variety of movies spanning multiple genres. Users can rate movies they've watched, providing valuable feedback on the content. With the diverse selection, understanding the average rating for each genre can help in content recommendation and analysis.
You're given the following tables:
movie table:
rating table:
Write a SQL query that returns a table listing each movie, its average rating and the average rating of its genre. The table should have columns for the movie title title, the average movie rating avg_rating, and the average genre rating genre_rating. Order the result by the movie title. Round ratings to 1 decimal place
In our approach, we first generate the MovieAverage CTE. In this CTE, we join the movie and rating tables to calculate the average rating for each movie. We use the ROUND() function to ensure that these average ratings are rounded to 1 decimal place. The result gives us the average rating for each movie title.
Next, we create the GenreAverage CTE. Here, we're again joining the movie and rating tables but this time to calculate the average rating for each genre. Just like before, we use the ROUND() function to round the computed averages to 1 decimal place. This CTE provides the average rating for each genre.
Finally, after preparing the two CTEs, we proceed to join them together. We first join the MovieAverage CTE with the movie table using the movie title to associate each movie with its genre. We then join the resulting table with the GenreAverage CTE using the genre to fetch the average genre rating.
The final SELECT statement gives us a table with columns for each movie title, its average rating (rounded to 1 decimal place), and the average rating of its genre (also rounded). We order the results by movie title to make it easier to read and interpret.
WITH MovieAverage AS (
-- Calculate the average rating for each movie
SELECT
m.title,
ROUND(AVG(r.rating), 1) as avg_rating
FROM movie m
JOIN rating r ON m.movie_id = r.movie_id
GROUP BY m.title
),
GenreAverage AS (
-- Calculate the average rating for each genre
SELECT
m.genre,
ROUND(AVG(r.rating), 1) as genre_rating
FROM movie m
JOIN rating r ON m.movie_id = r.movie_id
GROUP BY m.genre
)
-- Join the above results to get the final output
SELECT
ma.title,
ma.avg_rating,
ga.genre_rating
FROM MovieAverage ma
JOIN movie mo ON ma.title = mo.title
JOIN GenreAverage ga ON mo.genre = ga.genre
ORDER BY ma.title;
SELECT DISTINCT title, ROUND(AVG(rating) over (partition by title),1) avg_rating, ROUND(AVG(rating) over (partition by genre),1) genre_rating FROM rating r JOIN movie m ON r.movie_id=m.movie_id ORDER by 1