More practice problems with SQL
Find the top 5 highest-grossing genres (2010–2020) with an average rating above 7.0.
SELECT genre, SUM(box_office) AS total_box_office, AVG(rating) AS avg_rating FROM movies_sql_practice_dataset WHERE year BETWEEN 2010 AND 2020 GROUP BY genre HAVING AVG(rating) > 7 ORDER BY total_box_office DESC LIMIT 5;
--This was a really good one because it has multiple layers to it.
Which three genres have the highest total box office revenue?
SELECT genre, AVG(box_office) AS avg_by_genre FROM movies_sql_practice_dataset GROUP BY genre ORDER BY avg_by_genre DESC;
-- Learning to use more of the AVG function in sorting through data
Find the year with the highest average rating across all movies.
SELECT genre, MAX(rating) AS movie_rating FROM movies_sql_practice_dataset GROUP by genre ORDER by movie_rating DESC;
--Learning to use the MAX function here
What decade generated the most total box office revenue?
SELECT (year/10) *10 AS decade, SUM(box_office) AS total_revenue FROM movies_sql_practice_dataset GROUP BY decade order by decade;
--Learning how to create a function that sorts by decade. Really important to be able to sort through and break down larger time frames.
Find the average rating and average box office for each genre.
SELECT genre, AVG(rating) AS avg_rating, AVG(box_office) AS avg_box FROM movies_sql_practice_dataset GROUP BY genre ORDER BY avg_rating DESC;
--this was important to be able to quickly compare and contrast I think this is a function that I will be using a lot in the future. Being able to quickly compare and contrast data will be vital.