queries.txt

/*Querie 1 - query used for first insight*/
WITH t1 AS (SELECT f.title AS film_title, cat.name AS category_name, r.rental_id AS rental
              FROM category cat
              JOIN film_category f_cat
                ON f_cat.category_id = cat.category_id
              JOIN film f
                ON f_cat.film_id = f.film_id
              JOIN inventory i
                ON i.film_id = f.film_id
              JOIN rental r
                ON i.inventory_id = r.inventory_id
             WHERE cat.name IN ('Animation', 'Children', 'Classics', 'Comedy', 'Family', 'Music'))

SELECT DISTINCT film_title, category_name, 
                COUNT(*) OVER (PARTITION BY film_title) AS rental_count
           FROM t1
          ORDER BY 2, 1;

/*Querie 2 - query used for second insight*/
WITH t1 AS (SELECT f.title AS film_title, cat.name AS category_name, f.rental_duration AS rental_duration
              FROM category cat
              JOIN film_category f_cat
                ON f_cat.category_id = cat.category_id
              JOIN film f
                ON f_cat.film_id = f.film_id
             WHERE cat.name IN ('Animation', 'Children', 'Classics', 'Comedy', 'Family', 'Music'))

SELECT film_title, category_name, rental_duration,
       NTILE(4) OVER (ORDER BY rental_duration) AS standard_quartile
  FROM t1
 ORDER BY 3;

/*Querie 3 - query used for third insight*/
WITH t1 AS (SELECT DISTINCT cat.name AS category_name,
                            NTILE(4) OVER (ORDER BY rental_duration) AS standard_quartile,
                            f.film_id AS film_id
                       FROM category cat
                       JOIN film_category f_cat
                         ON f_cat.category_id = cat.category_id
                       JOIN film f
                         ON f_cat.film_id = f.film_id
                      WHERE cat.name IN ('Animation', 'Children', 'Classics', 'Comedy', 'Family', 'Music'))

SELECT category_name, standard_quartile,
       COUNT(*) AS count
  FROM t1
 GROUP BY 1, 2
 ORDER BY 1, 2;

/*Querie 4 - query used for fourth insight*/  
SELECT rental_month, rental_year, store_id, 
       COUNT(*) AS count_rental
  FROM (SELECT DATE_PART('month', r.rental_date) AS rental_month, DATE_PART('year', r.rental_date) AS rental_year,
               sto.store_id AS store_id, rental_id
          FROM category cat
          JOIN film_category f_cat
            ON f_cat.category_id = cat.category_id
          JOIN film f
            ON f_cat.film_id = f.film_id
          JOIN inventory i
            ON i.film_id = f.film_id
          JOIN rental r
            ON i.inventory_id = r.inventory_id
          JOIN staff sta
            ON r.staff_id = sta.staff_id
          JOIN store sto
            ON sta.store_id = sto.store_id) AS t1
 GROUP BY 1, 2, 3
 ORDER BY 4 DESC;