queries.txt

/*Question 1:
We want to understand more about the movies that families are watching. The following categories are considered family movies: Animation, Children, Classics, Comedy, Family and Music.

Create a query that lists each movie, the film category it is classified in, and the number of times it has been rented out.

Query 1
*/
SELECT
  film.title film_title,
  category.name category_name,
  SUM(rental.rental_id)
FROM film
JOIN film_category
  ON film.film_id = film_category.film_id
JOIN category
  ON category.category_id = film_category.category_id
JOIN rental
  ON rental.rental_id = film.film_id
GROUP BY 1,
         2
ORDER BY 3 DESC;

/*
Question 2:

Now we need to know how the length of rental duration of these family-friendly movies compares to the duration that all movies are rented for. 
Can you provide a table with the movie titles and divide them into 4 levels (first_quarter, second_quarter, third_quarter, and final_quarter) based on the quartiles (25%, 50%, 75%) of the rental duration for movies across all categories? 
Make sure to also indicate the category that these family-friendly movies fall into.

Query 2
*/

SELECT
  f.title film_title,
  c.name category_name,
  f.rental_duration AS rental_duration,
  NTILE(4) OVER (ORDER BY f.rental_duration) AS quartiles
FROM film f
JOIN film_category fc
  ON f.film_id = fc.film_id
JOIN category c
  ON c.category_id = fc.category_id
WHERE c.name IN ('Animation', 'Children', 'Classics', 'Comedy', 'Family', 'Music')
GROUP BY 1,
         2,
         3
ORDER BY 3 DESC;


/*Question 3:

provide a table with the family-friendly film category, each of the quartiles, and the corresponding count of movies within each combination of film category for each corresponding rental duration category.
The resulting table should have three columns:
Category
Rental length category
Count


Query 3:
*/

SELECT
  category_name,
  quartiles,
  COUNT(category_name)
FROM (SELECT
  c.name category_name,
  NTILE(4) OVER (ORDER BY f.rental_duration) AS quartiles
FROM film f
JOIN film_category fc
  ON f.film_id = fc.film_id
JOIN category c
  ON c.category_id = fc.category_id
WHERE c.name IN ('Animation', 'Children', 'Classics', 'Comedy', 'Family', 'Music')) t1
GROUP BY 1,
         2
ORDER BY 1, 2;



/*Question 4:

Write a query that returns the store ID for the store, the year and month and the number of rental orders each store has fulfilled for that month.
Your table should include a column for each of the following: year, month, store ID and count of rental orders fulfilled during that month.


Query 4*/

SELECT
  DATE_PART('YEAR', rental_date) YEARs,
  DATE_PART('MONTH', rental_date) MONTHs,
  store.store_id,
  COUNT(*)
FROM rental
JOIN payment
  ON payment.rental_id = rental.rental_id
JOIN staff
  ON staff.staff_id = payment.staff_id
JOIN store
  ON store.store_id = staff.store_id
GROUP BY 1,
         2,
         3
order by 4 desc;