Queries.txt

Question #1 - Family Movies with the Highest Rental Rate

WITH t1 AS 
    (
        SELECT i.film_id, COUNT(*) movie_rental_count
        FROM inventory i
        JOIN rental r
        ON r.inventory_id = i.inventory_id
        GROUP BY 1
    ),

    t2 AS
    (
        SELECT f.film_id, f.title, c.name
        FROM film f
        JOIN film_category fc
        ON fc.film_id = f.film_id
        JOIN category c
        ON fc.category_id = c.category_id
    )

SELECT t2.name, SUM(t1.movie_rental_count)
FROM t1
JOIN t2
ON t1.film_id = t2.film_id
GROUP BY 1
HAVING t2.name IN ('Animation', 'Children', 'Classics', 'Comedy', 'Family', 'Music')
ORDER BY 2 DESC




Question #2 - Family Movies Quartile Rental Rate

WITH t1 AS
    (
        SELECT c.name, NTILE(4) OVER (ORDER BY f.rental_duration) standard_quartile
        FROM category c
        JOIN film_category fc
        ON c.category_id = fc.category_id
        JOIN film f
        ON f.film_id = fc.film_id
    )

SELECT t1.name, t1.standard_quartile, COUNT(*) AS count
FROM t1
WHERE t1.name IN ('Animation', 'Children', 'Classics', 'Comedy', 'Family', 'Music')
GROUP BY 1,2
ORDER BY 1,2




Question #3 - Top 10 Paying Customers on Monthly Basis

SELECT 
    CONCAT(c.first_name, ' ', c.last_name) full_name, 
    DATE_TRUNC('month', p.payment_date) AS month, 
    COUNT(*) monthly_payment_count,
    SUM(p.amount) monthly_payment_total
FROM customer c
JOIN payment p
ON p.customer_id = c.customer_id
GROUP BY 1,2
ORDER BY 4 DESC
LIMIT 10




Question #4 - Monthly Difference of Top 10 Paying Customers

WITH t1 AS
    (
        SELECT 
            CONCAT(c.first_name, ' ', c.last_name) full_name, 
            DATE_TRUNC('month', p.payment_date) AS month, 
            COUNT(*) monthly_payment_count,
            SUM(p.amount) monthly_payment_total,
			c.customer_id
        FROM customer c
        JOIN payment p
        ON p.customer_id = c.customer_id
        GROUP BY 1,2,5
    ),

    t2 AS
    (
        SELECT customer_id, SUM(amount) total_amount
        FROM payment
        GROUP BY 1
        ORDER BY 2 DESC
        LIMIT 10
    )

SELECT 
    t1.full_name,
    t1.month,
    t1.monthly_payment_total,
    COALESCE(LEAD(t1.monthly_payment_total) OVER(PARTITION BY t1.full_name ORDER BY t1.monthly_payment_total), 0) AS lead,
	COALESCE((LEAD(t1.monthly_payment_total) OVER(PARTITION BY t1.full_name ORDER BY t1.monthly_payment_total)) - t1.monthly_payment_total, 0)
    AS monthly_payment_difference
FROM t1
JOIN t2
ON t1.customer_id = t2.customer_id
ORDER BY 1