# PostgreSQL database hands-on session 2 (solution)

## Initialize dvdrental demo database

1. See previous demo in [Session 1](https://hackmd.io/CHBzTAb1QxaYrg2WctNasA?view).
2. Main tutorial page for [PostgreSQL](https://www.postgresqltutorial.com/postgresql-getting-started/).

### Exercises and solutions

1. **Grouping Data** [[Tutorial link]](https://www.postgresqltutorial.com/postgresql-group-by/)
a. Find the top (or first) 10 customers’ IDs who spend more money than the average spendings of all customers. Hint: "GROUP BY" and "AVG".

    SELECT customer_id, AVG(amount) 
    FROM payment
    GROUP BY customer_id
    Having AVG(amount)>4.2006056453822965 -- (SELECT AVG(amount)  FROM payment)
    ORDER BY AVG(amount) LIMIT 10;
    -- Top 10 by using DESC

b. Challenge: Find customers’ full names who spend more money than the average spendings of all customers. (Hint: "GROUP BY" and "JOIN")

    SELECT c.first_name || ' ' || c.last_name AS full_name, AVG (p.amount) AS amount
    FROM payment AS p
    INNER JOIN customer AS c on p.customer_id = c.customer_id
    GROUP BY full_name
	Having AVG(p.amount)>4.2006056453822965
    ORDER BY AVG (p.amount);

2. **Set Operations** [[Tutorial link]](https://www.postgresqltutorial.com/postgresql-union/)
a. Find customers’ IDs whose total amount (SUM(amount) for each customer_id in payment table) have been spent more than 200 or less than 40. (Hint: "UNION")

    SELECT customer_id, SUM (amount)
    FROM payment
    GROUP BY customer_id
    HAVING SUM (amount) < 40
    UNION ALL
    SELECT customer_id, SUM (amount)
    FROM payment
    GROUP BY customer_id
    HAVING SUM (amount) > 200 ;

b. Challenges: Find customers’ IDs who have spent more than 90 total amount (SUM(amount) for each customer_id in payment table) but less than 20-time payments (COUNT(amount) for each customer_id in payment table). (Hint: "INTERSECTION")

    SELECT customer_id
    FROM payment
    GROUP BY customer_id
    HAVING COUNT (amount) < 20
    INTERSECT
    SELECT customer_id
    FROM payment
    GROUP BY customer_id
    HAVING SUM (amount) > 90 ;

3. **Grouping sets, Cube, and Rollup** [[Tutorial link]](https://www.postgresqltutorial.com/postgresql-grouping-sets/)
a. Find customers’ last names who have more than 20-time payments. (Hint: use subquery and “IN”)

    SELECT last_name
    FROM customer 
    WHERE customer_id IN (
        SELECT customer_id
        FROM payment
        GROUP BY customer_id
        HAVING COUNT (amount) > 20)
    limit 10;

b. Challenge: Find (optional: top/first 10) customers’ IDs whose total payment amounts are more than each customer’s average total amount. (Hint: use subquery and “AVG”)

    SELECT customer_id FROM payment GROUP BY customer_id
    HAVING SUM(amount) > 
        (SELECT AVG(total_amount) 
        FROM (SELECT SUM(amount) total_amount 
        FROM payment group by customer_id) AS just_a_name)
    LIMIT 10;

4. **Subquery** [[Tutorial link]](https://www.postgresqltutorial.com/postgresql-subquery/)
a. Challenge: The following finds the films whose lengths are greater than or equal to the maximum length of any film category:

    SELECT title, length FROM film 
    WHERE length >= ANY(
        SELECT MAX(length) FROM film
        INNER JOIN film_category USING(film_id)
        GROUP BY category_id) 
    LIMIT 10;

Rewrite the above query without “ANY” keyword to implement the exact same function. (Hint: use “MIN” instead of “MAX”.)

    SELECT title, length
    FROM film
    WHERE length >= (SELECT MIN(max_length) FROM (
        SELECT MAX( length ) AS max_length
        FROM film
        INNER JOIN film_category USING(film_id)
        GROUP BY  category_id ) AS foo) 
    LIMIT 10;

5. **Common Table Expressions** [[Tutorial link]](https://www.postgresqltutorial.com/postgresql-cte/)
a. Challenge: Define a CTE that returns customers’ IDs and amounts ranking by the amount of each payment, then find the customers’ IDs and amounts whose ranking are one. (HINT: "WITH", "RANK() OVER")

    WITH cte_payment AS (
        SELECT customer_id, amount,
        RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC) amount_rank
        FROM payment)
    SELECT *
    FROM cte_payment
    WHERE amount_rank = 1;