Practice: compose queries

How to use this page

Connect with psql shop. Write your query, run it, then compare with ours. Matching rows is what counts; your query can look different. Exclude cancelled orders wherever the question talks about spending.

The questions

1. Which products are priced above the average price of their own category?

   Show answer

   A correlated scalar subquery: the average is recomputed for each product’s category.

   SELECT p.name, p.category, p.price
   FROM products p
   WHERE p.price > (SELECT avg(q.price) FROM products q WHERE q.category = p.category)
   ORDER BY p.category, p.price DESC;

           name         |  category   |  price
   ---------------------+-------------+----------
    Mechanical keyboard | Accessories |  4500.00
    USB-C hub           | Accessories |  2499.00
    Office chair        | Furniture   | 14500.00

   Categories with a single product cannot have a product above their own average.

   I could explain this without looking

2. Which customers spent more than the average customer?

   Show answer

   Two levels: total per customer, then the average of those totals.

   SELECT name, spent
   FROM (
     SELECT c.name, sum(oi.quantity * oi.unit_price) AS spent
     FROM customers c
     JOIN orders o       ON o.customer_id = c.id
     JOIN order_items oi ON oi.order_id = o.id
     WHERE o.status <> 'cancelled'
     GROUP BY c.id, c.name
   ) AS per_customer
   WHERE spent > (
     SELECT avg(spent) FROM (
       SELECT sum(oi.quantity * oi.unit_price) AS spent
       FROM orders o JOIN order_items oi ON oi.order_id = o.id
       WHERE o.status <> 'cancelled'
       GROUP BY o.customer_id
     ) AS s
   )
   ORDER BY spent DESC;

       name    |  spent
   ------------+----------
    Ben Okafor | 41997.00
    Divya Iyer | 26695.00
    Farhan Ali | 24998.00

   The per-customer query appears twice. Common table expressions show how to name it once and reuse it.

   I could explain this without looking

3. Which orders contain at least one Accessories product?

   Show answer

   SELECT o.id, o.ordered_at
   FROM orders o
   WHERE EXISTS (
     SELECT 1 FROM order_items oi JOIN products p ON p.id = oi.product_id
     WHERE oi.order_id = o.id AND p.category = 'Accessories'
   )
   ORDER BY o.id;

   Six orders: 101, 103, 107, 109, 110 and 111. Order 103 has three accessories and still appears once.

   I could explain this without looking

4. What is each customer’s most recent order?

   Show answer

   SELECT c.name, o.id, o.ordered_at
   FROM customers c
   JOIN orders o ON o.customer_id = c.id
   WHERE o.ordered_at = (SELECT max(o2.ordered_at) FROM orders o2 WHERE o2.customer_id = c.id)
   ORDER BY c.name;

       name    | id  |       ordered_at
   ------------+-----+------------------------
    Asha Rao   | 108 | 2025-07-03 06:00:00+00
    Ben Okafor | 106 | 2025-06-01 07:20:00+00
    Chen Wei   | 110 | 2025-08-02 05:25:00+00
    Divya Iyer | 111 | 2025-08-28 14:25:00+00
    Farhan Ali | 112 | 2025-09-09 05:00:00+00
    Grace Kim  | 109 | 2025-07-19 11:10:00+00

   If a customer had two orders at exactly the same moment, both would appear. Window functions give a way to pick exactly one.

   I could explain this without looking

5. Which order items were sold below the product’s current list price?

   Show answer

   No subquery needed: comparing two columns from joined tables answers it.

   SELECT oi.order_id, p.name, oi.unit_price, p.price AS list_price
   FROM order_items oi
   JOIN products p ON p.id = oi.product_id
   WHERE oi.unit_price < p.price
   ORDER BY oi.order_id;

    order_id |            name             | unit_price | list_price
   ----------+-----------------------------+------------+------------
         103 | Wireless mouse              |    1100.00 |    1200.00
         108 | Noise-cancelling headphones |   11999.00 |   12999.00

   This is why order_items stores the price paid: the catalogue price has moved on.

   I could explain this without looking

Where this section leaves you

You can now read a schema, set up PostgreSQL, query, change data safely, choose types, enforce rules with constraints, join tables without multiplying rows by accident, summarize with grouping, and compose queries from smaller ones. The next section, Data modeling and advanced SQL, builds on every one of these.