/* Question Set #1 */
/* Question 1 query */

WITH family_movies AS (
  SELECT f.film_id,
         f.title,
         c.name
  FROM film AS f
  JOIN film_category AS fc
    ON fc.film_id = f.film_id
  JOIN category AS c
    ON c.category_id = fc.category_id
  WHERE c.name IN ('Animation', 'Children', 'Classics', 'Comedy', 'Family', 'Music')
)

SELECT DISTINCT(title) AS film_title,
       name AS category_name,
       COUNT(title) OVER (PARTITION BY title) AS rental_count
FROM family_movies
JOIN inventory AS i
  ON i.film_id = family_movies.film_id
JOIN rental AS r
  ON r.inventory_id = i.inventory_id
ORDER BY 2, 1

/* Question 2 query */

WITH family_movies AS (
  SELECT f.film_id,
         f.title,
         c.name,
         f.rental_duration
  FROM film AS f
  JOIN film_category AS fc
    ON fc.film_id = f.film_id
  JOIN category AS c
    ON c.category_id = fc.category_id
  WHERE c.name IN ('Animation', 'Children', 'Classics', 'Comedy', 'Family', 'Music')
),
percentiles AS (
  SELECT film_id,
         rental_duration,
         NTILE(4) OVER (ORDER BY rental_duration) AS standard_quartile
  FROM film
)

SELECT f.title AS film_title,
       f.name AS category_name,
       f.rental_duration,
       p.standard_quartile
FROM family_movies AS f
JOIN percentiles AS p
  ON f.film_id = p.film_id
ORDER BY 4, 1

/* Question 3 */

WITH family_movies AS (
  SELECT f.film_id,
         f.title,
         c.name,
         f.rental_duration
  FROM film AS f
  JOIN film_category AS fc
    ON fc.film_id = f.film_id
  JOIN category AS c
    ON c.category_id = fc.category_id
  WHERE c.name IN ('Animation', 'Children', 'Classics', 'Comedy', 'Family', 'Music')
),
percentiles AS (
  SELECT film_id,
         rental_duration,
         NTILE(4) OVER (ORDER BY rental_duration) AS standard_quartile
  FROM film
),
family_perc AS (
  SELECT f.title AS film_title,
         f.name AS category_name,
         f.rental_duration,
         p.standard_quartile
  FROM family_movies AS f
  JOIN percentiles AS p
    ON f.film_id = p.film_id
)

SELECT category_name,
       standard_quartile,
       COUNT(*)
FROM family_perc
GROUP BY 1, 2
ORDER BY 1, 2

/* Question Set #2 */
/* Question 1 */

SELECT DATE_PART('month', r.rental_date) AS rental_month,
       DATE_PART('year', r.rental_date) AS rental_year,
       i.store_id,
       COUNT(*) AS count_rentals
FROM inventory AS i
JOIN rental AS r
  ON r.inventory_id = i.inventory_id
GROUP BY 1, 2, 3
ORDER BY 4 DESC