Skip to content

Data-Driven Decision Making in SQL

Here you can access every table used in the course. To access each table, you will need to specify the movies schema in your queries (e.g., movies.movies for the movies table, and movies.customers for the customers table).

Take Notes

Add notes about the concepts you've learned and SQL cells with queries you want to keep.

Add your notes here Average rating per customer Similar to what you just did, you will now look at the average movie ratings, this time for customers. So you will obtain a table with the average rating given by each customer. Further, you will include the number of ratings and the number of movie rentals per customer. You will report these summary statistics only for customers with more than 7 movie rentals and order them in ascending order by the average rating.

Instructions 100 XP Group the data in the table renting by customer_id and report the customer_id, the average rating, the number of ratings and the number of movie rentals. Select only customers with more than 7 movie rentals. Order the resulting table by the average rating in ascending order.

Spinner
DataFrameas
rating
variable
SELECT customer_id, -- Report the customer_id
      AVG(rating),  -- Report the average rating per customer
      COUNT (rating),  -- Report the number of ratings per customer
     COUNT(*)  -- Report the number of movie rentals per customer
FROM renting
GROUP BY customer_id
HAVING COUNT(*)>7 -- Select only customers with more than 7 movie rentals
ORDER BY AVG(rating); -- Order by the average rating in ascending order
Spinner
DataFrameas
actors
variable
-- Add your own queries here
SELECT *
FROM movies.actors
LIMIT 5
Spinner
DataFrameas
age_of_actors
variable
SELECT a.nationality,a.gender, -- Report for male and female actors from the USA 
       MIN(year_of_birth), -- The year of birth of the oldest actor
       MAX(year_of_birth)-- The year of birth of the youngest actor
       
FROM
   (SELECT * -- Use a subsequen SELECT to get all information about actors from the USA
   FROM actors
   WHERE nationality='USA'AND (gender ='female' OR gender='male')) 
    AS a -- Give the table the name a
GROUP BY a.nationality, a.gender;

Age of actors from the USA Now you will explore the age of American actors and actresses. Report the date of birth of the oldest and youngest US actor and actress.

Instructions 100 XP Create a subsequent SELECT statements in the FROM clause to get all information about actors from the USA. Give the subsequent SELECT statement the alias a. Report for actors from the USA the year of birth of the oldest and the year of birth of the youngest actor and actress.

Examining annual rentals You are asked to provide a report about the development of the company. Specifically, your manager is interested in the total number of movie rentals, the total number of ratings and the average rating of all movies since the beginning of 2019.

SELECT COUNT(*) AS number_renting, AVG(rating) AS average_rating, COUNT (rating) AS number_ratings -- Add the total number of ratings here. FROM renting WHERE date_renting >= '2019-01-01';