Data-Driven Decision Making in SQL
  • AI Chat
  • Code
  • Report
    • 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).

    Unknown integration
    DataFrameavailable as
    df
    variable
    -- check the tables in the table_schema='movies'
SELECT table_name, table_schema, table_type
FROM information_schema.tables
where table_schema = 'movies'
ORDER BY table_name ASC;
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

    Screen the tables and the columns

    Unknown integration
    DataFrameavailable as
    actors
    variable
    -- explore the tables
SELECT *
FROM movies.actors
LIMIT 5
    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
    df
    variable
    select * 
from movies.movies
limit 5
    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
    df
    variable
    select *
from movies.renting 
limit 5
    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
    df
    variable
    select *
from movies.customers
limit 5
    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
    df
    variable
    select *
from movies.actsin
limit 5
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

    Explore the renting table to see the number of renting records, number of distinct customer, and number of ratings

    Unknown integration
    DataFrameavailable as
    df
    variable
    --renting table is the main table which store the renting data for the store
select 
count(*) as no_renting
, count(distinct customer_id) as unique_customer
, count(rating) as no_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.

    Average rating per movie

    For each movie the average rating, the number of ratings and the number of views has to be reported
    Join movies table for the movie title

    Unknown integration
    DataFrameavailable as
    df
    variable
    select
m.title
, avg(rating) as avg_rating
, count(rating) as no_rating
, count(*) as no_renting
from movies.renting as r
left join movies.movies as m
ON r.movie_id = m.movie_id
group by m.title
having  avg(rating) is not null   -- after grouping by title, remove the movies which do not have any avg_rating
order by avg_rating DESC          --check the highest rating movie
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

    Average rating per customer

    We will report these summary statistics only for customers with more than 7 movie rentals and order them in ascending order by the average rating.

    Unknown integration
    DataFrameavailable as
    df
    variable
    select 
c.name
, avg(r.rating) as avg_rating
, count(r.rating) as no_rating
, count(*) as no_renting
from movies.renting as r
left join movies.customers as c
ON r.customer_id = c.customer_id
group by c.name
having count(*) >7
order by no_renting DESC
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
    Current Type: Bar
    Current X-axis: no_renting
    Current Y-axis: name
    Current Color: None

    Top renting movies

    ‌
    ‌
    ‌