ironhack-labs / lab-sql-9

Lab | SQL Queries 9

In this lab, you will be using the Sakila database of movie rentals. You have been using this database for a couple labs already, but if you need to get the data again, refer to the official installation link.

The database is structured as follows:

Instructions

In this lab we will find the customers who were active in consecutive months of May and June. Follow the steps to complete the analysis.

• Create a table rentals_may to store the data from rental table with information for the month of May.

• Insert values in the table rentals_may using the table rental, filtering values only for the month of May.

• Create a table rentals_june to store the data from rental table with information for the month of June.

• Insert values in the table rentals_june using the table rental, filtering values only for the month of June.

• Check the number of rentals for each customer for May.

• Check the number of rentals for each customer for June.

• Create a Python connection with SQL database and retrieve the results of the last two queries (also mentioned below) as dataframes:

  • Check the number of rentals for each customer for May

  • Check the number of rentals for each customer for June

    Hint: You can store the results from the two queries in two separate dataframes.

• Write a function that checks if customer borrowed more or less films in the month of June as compared to May.

  Hint: For this part, you can create a join between the two dataframes created before, using the merge function available for pandas dataframes. Here is a link to the documentation for the merge function.

lab-sql-9's Issues

Task Unclear

@sandrabosk

Last task is unclear, students couldn't figure out. Also, they are trying to get everything in one query with lots of joins (check if that is ok). Also, 'most rented film category by client' solution suggestion is not returning the desired result. Solved it with subqueries:

SELECT customer, category_name FROM
(SELECT rental.customer_id as customer, count(rental.rental_id) as total_rentals, film_category.category_id, category.name as category_name,
row_number() over (partition by rental.customer_id order by count(rental.rental_id) desc) as ranking_max_rented_category
FROM rental
INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id
INNER JOIN film_category ON inventory.film_id = film_category.film_id
INNER JOIN category ON film_category.category_id = category.category_id
GROUP BY rental.customer_id, film_category.category_id, category.name) AS table_popular_category
WHERE ranking_max_rented_category = 1
ORDER BY customer;