Workspace
Faith Eshiet/

Data-Driven Decision Making in SQL

0
Beta
Spinner

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).

Throughout this course, we will work with a Postgres database from a fictional movie rental company called MovieNow. MovieNow offers an online platform for streaming movies. Customers can rent a movie for 24 hours. For all movies, the company stores additional information such as the genre or the main actors. MovieNow also stores information about customers and movie ratings.

MOVIENOW DATA STRUCTURE

An overview of the tables in the database:

Customers Table: columns 'customer_id', a number which is a unique identifier for each customer, 'name', 'country', 'gender', 'date_of_birth', and the final column is the date when the account for MovieNow was created, 'date_account_start'.

Movies Table: columns include a unique identifier movie_id, the title of the movie, the movie genre, the runtime, the release year, and, finally, what it costs to rent the movie.

The table 'renting' records all movie rentals. 'renting_id' is a unique identifier for each movie rental. The column 'customer_id' tells us which customer rented the movie and 'movie_id' tells us which movie the customer rented. The rating a customer gives after watching the movie is stored in the column 'rating' which has values between 1 and 10, where 10 is the best rating. The final column is the rental date.

The table 'actors' contains information about the actors in the movies. Besides the unique identifier 'actor_id', we have the actor's name, year of birth, nationality, and gender.

The table 'actsin' shows which actor appears in which movie. Besides the unique identifier actsin_id, it includes movie_id and actor_id.

Exploring the table renting

Select all columns from renting.

Unknown integration
DataFrameavailable as
df
variable
SELECT *
FROM movies.renting;
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

Columns from renting which are needed to calculate the average rating per movie.

Unknown integration
DataFrameavailable as
df1
variable
SELECT movie_id, rating
FROM movies.renting;
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

Which is the most recently rented movie from movienow?

Unknown integration
DataFrameavailable as
df2
variable
SELECT movie_id, date_renting
FROM movies.renting
ORDER BY date_renting DESC;
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
Unknown integration
DataFrameavailable as
df3
variable
SELECT title
FROM movies.movies
WHERE movie_id = 50;
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

Winter's Bone is the most recently rented movie from MovieNow.

Which is the most expensive movie to rent?

Unknown integration
DataFrameavailable as
df4
variable
SELECT *
FROM movies.movies
ORDER BY renting_price DESC;
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

Morning Glory and 11'09''01 September 11 are the most expensive movies to rent. with the cost of 2.99 as renting price.

Only some users give a rating after watching a movie. Let's explore only those movie rentals where a rating was provided.




  • AI Chat
  • Code