---
# System prepended metadata

title: PostgreSQL database hands-on session 2 (solution)

---

# 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".
    ```SQL
    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")
    ```SQL
    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")
    ```SQL
    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")
    ```SQL
    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”)
    ```SQL
    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”)
    ```SQL
    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:
    
    ```SQL
    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”.)
    ```SQL
    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")
    ```SQL
    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;
    ```

