Exercise 1
The most successful series
🎯 Identify the most successful series in NetflixDB, where success is defined by the number of episodes and the average IMDb rating. The result is a table with three columns: series title, number of episodes and average IMDb rating.
JOINCOUNTAVGGROUP BY
SELECT
s.titulo AS titulo_serie,
COUNT(e.episodio_id) AS cantidad_episodios,
ROUND(AVG(e.rating_imdb), 2) AS rating_promedio_imdb
FROM Series s
JOIN Episodios e ON e.serie_id = s.serie_id
GROUP BY s.serie_id, s.titulo
ORDER BY cantidad_episodios DESC, rating_promedio_imdb DESC;
How it works: I join Series with Episodios on serie_id, group by series and count its episodes while averaging the rating. Ordering by episode count and rating brings the most successful series to the top.
Exercise 2
The 3 most popular genres (with a subquery)
🎯 Use a subquery to identify the three most popular genres (by number of series). For each genre, show the series title, release year and average IMDb rating.
SubqueryINJOINGROUP BYLIMIT
SELECT
s.genero,
s.titulo,
s.año_lanzamiento,
ROUND(AVG(e.rating_imdb), 2) AS rating_promedio_imdb
FROM Series s
JOIN Episodios e ON e.serie_id = s.serie_id
WHERE s.genero IN (
SELECT genero
FROM Series
GROUP BY genero
ORDER BY COUNT(serie_id) DESC
LIMIT 3
)
GROUP BY s.serie_id, s.genero, s.titulo, s.año_lanzamiento
ORDER BY s.genero, rating_promedio_imdb DESC;
How it works: The WHERE subquery returns the three genres with the most series. The main query keeps only the series in those genres and, joining with Episodios, computes each one’s average rating, showing genre, title and year.