Subqueries, CTEs and Window Functions

Questions Inside Questions
Scalar Subqueries
Subqueries With IN and EXISTS
Derived Tables: A Subquery in FROM
CTEs: Naming Your Steps With WITH
Views: Saving a Query
Window Functions: Calculating Without Collapsing
ROW_NUMBER, RANK and DENSE_RANK
Running Totals With SUM OVER
PARTITION BY: Windows per Group
Top-N per Group
UNION, INTERSECT and EXCEPT
Try It Yourself

1. Questions Inside Questions

Some questions cannot be answered in one pass. "Which products cost more than the average product?" needs two steps: first work out the average, then compare each product to it. "Who are our best customers by total spend?" needs a total per customer, and then a ranking of those totals.

SQL lets you nest a query inside another (a subquery), give a query a name and use it later (a CTE), or even save one permanently (a view). Last, window functions let you compute things like rank and running totals while keeping every row visible. These are the tools that separate basic SQL from the real work analysts and backend developers do.

Save this chapter's queries in shopdb/queries/09_subqueries_windows.sql and run them the usual way:

docker exec -i shopdb-pg psql -U postgres -d shopdb < queries/09_subqueries_windows.sql

2. Scalar Subqueries

A scalar subquery returns exactly one value (one row, one column). You can use it anywhere a single value fits, such as in a WHERE comparison.

Products priced above the average:

SELECT name, price
FROM products
WHERE price > (SELECT avg(price) FROM products)
ORDER BY price DESC;

        name         | price
---------------------+-------
 Mechanical Keyboard | 79.00
 SQL Cookbook        | 32.00
 Clean Code          | 28.50
(3 rows)

The inner query runs first and produces 24.6875. The outer query then keeps products priced above that. If the subquery returned more than one row, PostgreSQL would raise ERROR: more than one row returned by a subquery used as an expression, so be sure it is a true single value.

3. Subqueries With IN and EXISTS

A subquery can also return a list of values, used with IN. Which customers have a paid order?

SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE status = 'paid')
ORDER BY id;

    name
------------
 Sokha Chan
 Linda Vong
(2 rows)

EXISTS asks a yes/no question per outer row: "does at least one row exist in this subquery?" The subquery refers to the outer row, which is called a correlated subquery. Which customers have a cancelled order?

SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
    AND o.status = 'cancelled'
);

   name
----------
 Dara Kim
(1 row)

For each customer, the inner query is conceptually re-run with that customer's c.id. (In practice PostgreSQL turns this into an efficient semi-join, not a loop.) Use NOT EXISTS for the opposite, the anti-join from chapter 7.

IN and EXISTS often give the same result. A good guide: use IN for a simple list of values, and EXISTS when you need to correlate with the outer row or when the subquery column could be NULL.

4. Derived Tables: A Subquery in FROM

A subquery in the FROM clause behaves like a temporary table. It must have an alias. This is how you solve "aggregate, then do something with the aggregate":

SELECT c.name, t.total
FROM customers c
JOIN (
  SELECT o.customer_id, sum(oi.quantity * oi.unit_price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY o.customer_id
) t ON t.customer_id = c.id
ORDER BY t.total DESC;

    name    | total
------------+--------
 Sokha Chan | 105.00
 Dara Kim   |  82.50
 Linda Vong |  57.50
(3 rows)

The inner query makes one row per customer id, with their total. The outer query attaches names. Because the aggregation happened inside before joining to anything else, there is no row multiplication problem.

This works, but notice how it reads inside-out. When subqueries nest two or three levels deep, they become hard to follow. That is the reason CTEs exist.

5. CTEs: Naming Your Steps With WITH

A common table expression (CTE) gives a subquery a name and puts it before the main query, so you read top to bottom like a recipe.

WITH order_totals AS (
  SELECT o.id AS order_id,
         o.customer_id,
         o.status,
         sum(oi.quantity * oi.unit_price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY o.id, o.customer_id, o.status
)
SELECT * FROM order_totals ORDER BY order_id;

 order_id | customer_id |  status   | total
----------+-------------+-----------+-------
        1 |           1 | paid      | 26.00
        2 |           1 | shipped   | 79.00
        3 |           2 | pending   | 60.50
        4 |           3 | paid      | 57.50
        5 |           2 | cancelled | 22.00
(5 rows)

Now order_totals is a named, temporary table you can reuse. Build the next step on top of it: the number of orders and average order value for each customer, ignoring cancelled ones.

WITH order_totals AS (
  SELECT o.id AS order_id,
         o.customer_id,
         o.status,
         sum(oi.quantity * oi.unit_price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY o.id, o.customer_id, o.status
)
SELECT c.name,
       count(*)                AS orders,
       round(avg(t.total), 2)  AS avg_order
FROM order_totals t
JOIN customers c ON c.id = t.customer_id
WHERE t.status <> 'cancelled'
GROUP BY c.name
ORDER BY c.name;

    name    | orders | avg_order
------------+--------+-----------
 Dara Kim   |      1 |     60.50
 Linda Vong |      1 |     57.50
 Sokha Chan |      2 |     52.50
(3 rows)

Sokha's two orders are 26.00 and 79.00, average 52.50. Dara's cancelled order is excluded, so only her pending 60.50 remains.

You can define several CTEs separated by commas, each able to use the earlier ones:

WITH step_one AS (...),
     step_two AS (SELECT ... FROM step_one ...)
SELECT ... FROM step_two;

Benefits: each step has a name that describes it, you can test each piece alone by selecting from it, and the same CTE can be referenced more than once. A CTE exists only for that one statement. (There is a more advanced WITH RECURSIVE form for walking trees and hierarchies, which we skip here.)

6. Views: Saving a Query

The order_totals query is so useful that you will want it in many places. A view is a saved query with a name. You can then select from it as if it were a table, but it always runs fresh against the current data. It stores no rows of its own.

Add this to the end of shopdb/schema.sql, so a rebuild recreates it after the tables:

CREATE OR REPLACE VIEW order_totals AS
SELECT o.id AS order_id,
       o.customer_id,
       o.status,
       o.ordered_at,
       sum(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, o.customer_id, o.status, o.ordered_at;

Then, from psql:

SELECT * FROM order_totals WHERE status = 'paid' ORDER BY order_id;

 order_id | customer_id | status |      ordered_at        | total
----------+-------------+--------+------------------------+-------
        1 |           1 | paid   | 2026-01-05 00:00:00+00 | 26.00
        4 |           3 | paid   | 2026-02-10 00:00:00+00 | 57.50
(2 rows)

(The timestamp format and time zone offset depend on your server settings. In the default Docker image it is UTC, so you will typically see +00.)

Views are useful for three reasons:

Reuse: one definition, used by every report.
Simplicity: hide a five-table join behind one friendly name.
Consistency: "revenue" means the same thing everywhere.

\dv lists your views in psql, and DROP VIEW order_totals; removes one. Plain views do not make queries faster, they just save typing. (A MATERIALIZED VIEW does store its result for speed, but must be refreshed. That is a topic for later.)

7. Window Functions: Calculating Without Collapsing

GROUP BY collapses rows: five orders by customer become three rows, and the individual orders are gone. But often you want a calculation and the original rows: each order with its rank, or each order with the running total so far.

A window function does exactly this. It computes a value across a set of related rows (the "window") but returns one result per input row. You recognise it by the OVER (...) clause.

SELECT name,
       price,
       round(price - avg(price) OVER (), 2) AS diff_from_avg
FROM products
ORDER BY id;

        name         | price | diff_from_avg
---------------------+-------+---------------
 Wireless Mouse      | 15.00 |         -9.69
 USB-C Cable         |  5.50 |        -19.19
 Mechanical Keyboard | 79.00 |         54.31
 SQL Cookbook        | 32.00 |          7.31
 Clean Code          | 28.50 |          3.81
 Desk Lamp           | 22.00 |         -2.69
 Coffee Mug          |  6.00 |        -18.69
 Notebook Pack       |  9.50 |        -15.19
(8 rows)

avg(price) OVER () is the average of the whole table (24.6875), shown next to every row. Empty parentheses mean "the window is all rows". Compare with GROUP BY, which could only give you the single average row. All eight products are still here.

8. ROW_NUMBER, RANK and DENSE_RANK

Ranking functions number the rows according to an order you choose inside OVER (ORDER BY ...). Rank our orders from biggest to smallest, reusing the view from section 6:

SELECT order_id,
       total,
       row_number() OVER (ORDER BY total DESC) AS rn
FROM order_totals
ORDER BY rn;

 order_id | total | rn
----------+-------+----
        2 | 79.00 |  1
        3 | 60.50 |  2
        4 | 57.50 |  3
        1 | 26.00 |  4
        5 | 22.00 |  5
(5 rows)

The three ranking functions differ only in how they treat ties. Imagine three exam scores of 90, 90 and 80:

score	`ROW_NUMBER()`	`RANK()`	`DENSE_RANK()`
90	1	1	1
90	2	1	1
80	3	3	2

ROW_NUMBER always gives unique numbers, breaking ties arbitrarily.
RANK gives tied rows the same rank and then skips (1, 1, 3).
DENSE_RANK gives tied rows the same rank and does not skip (1, 1, 2).

Our order totals have no ties, so they all agree here. Pick by meaning: "the 3rd best row" wants ROW_NUMBER, a leaderboard wants RANK.

9. Running Totals With SUM OVER

When an aggregate function has OVER (ORDER BY ...), it becomes a running aggregate: the total of every row up to and including the current one.

SELECT order_id,
       ordered_at::date AS day,
       total,
       sum(total) OVER (ORDER BY ordered_at, order_id) AS running_total
FROM order_totals
ORDER BY ordered_at, order_id;

 order_id |    day     | total | running_total
----------+------------+-------+---------------
        1 | 2026-01-05 | 26.00 |         26.00
        2 | 2026-01-20 | 79.00 |        105.00
        3 | 2026-02-03 | 60.50 |        165.50
        4 | 2026-02-10 | 57.50 |        223.00
        5 | 2026-02-15 | 22.00 |        245.00
(5 rows)

Each row adds its own total to everything before it: 26 + 79 = 105, then + 60.50 = 165.50, and so on. The last row, 245.00, is the sum of all orders (including the cancelled one, since we did not filter).

Adding order_id to the ORDER BY inside OVER is a good habit. If two orders had the same timestamp, it makes the running order, and so the numbers, deterministic.

The same idea gives you lag() and lead(), which read the previous or next row's value (handy for "days since the customer's last order"), and count(*) OVER ... for running counts.

10. PARTITION BY: Windows per Group

PARTITION BY splits the rows into groups, and the window function restarts in each one. It is like GROUP BY for window functions, but without collapsing the rows.

A running total per customer:

SELECT customer_id,
       order_id,
       total,
       sum(total) OVER (
         PARTITION BY customer_id
         ORDER BY ordered_at, order_id
       ) AS customer_running
FROM order_totals
ORDER BY customer_id, ordered_at;

 customer_id | order_id | total | customer_running
-------------+----------+-------+------------------
           1 |        1 | 26.00 |            26.00
           1 |        2 | 79.00 |           105.00
           2 |        3 | 60.50 |            60.50
           2 |        5 | 22.00 |            82.50
           3 |        4 | 57.50 |            57.50
(5 rows)

The total restarts for each customer. Customer 1 reaches 105.00, customer 2 reaches 82.50 (matching the revenue report from the last chapter), and customer 3 has one order.

The pattern to remember:

function() OVER (PARTITION BY <groups> ORDER BY <order within group>)

PARTITION BY is optional (omit it for one big window) and so is ORDER BY (omit it and the function sees the whole partition).

11. Top-N per Group

A classic interview and real-world question: "the two most expensive products in each category." LIMIT 2 cannot do it, because it limits the whole result, not each group. Window functions can: number the rows within each category, then keep the first two.

SELECT category_id, name, price
FROM (
  SELECT category_id, name, price,
         row_number() OVER (
           PARTITION BY category_id
           ORDER BY price DESC
         ) AS rn
  FROM products
) ranked
WHERE rn <= 2
ORDER BY category_id, price DESC;

 category_id |        name         | price
-------------+---------------------+-------
           1 | Mechanical Keyboard | 79.00
           1 | Wireless Mouse      | 15.00
           2 | SQL Cookbook        | 32.00
           2 | Clean Code          | 28.50
           3 | Desk Lamp           | 22.00
           3 | Coffee Mug          |  6.00
(6 rows)

Why the wrapper query? A window function's value is computed in the SELECT step, which happens after WHERE. So you cannot write WHERE row_number() OVER (...) <= 2 directly. You compute rn in an inner query, a derived table or a CTE, and filter on it outside. The same query as a CTE reads more cleanly:

WITH ranked AS (
  SELECT category_id, name, price,
         row_number() OVER (PARTITION BY category_id ORDER BY price DESC) AS rn
  FROM products
)
SELECT category_id, name, price
FROM ranked
WHERE rn <= 2
ORDER BY category_id, price DESC;

Use RANK() instead of ROW_NUMBER() if you want ties for the last place to be kept. This shape (rank inside a partition, filter on the rank) solves "latest order per customer", "best seller per month", and many more.

12. UNION, INTERSECT and EXCEPT

Joins combine tables side by side (more columns). Set operations stack query results on top of each other (more rows). The two queries must return the same number of columns with compatible types.

Operator	Result
`UNION`	rows in either query, duplicates removed
`UNION ALL`	rows in either query, duplicates kept (faster)
`INTERSECT`	rows in both queries
`EXCEPT`	rows in the first but not the second

Customers from Phnom Penh or with a paid order:

SELECT name FROM customers WHERE city = 'Phnom Penh'
UNION
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE status = 'paid')
ORDER BY name;

    name
------------
 Linda Vong
 Sokha Chan
(2 rows)

Both groups happen to be the same two people. UNION removed the duplicates, so each name appears once. With UNION ALL you would see four rows.

EXCEPT is another way to find customers who never ordered:

SELECT id FROM customers
EXCEPT
SELECT customer_id FROM orders
ORDER BY id;

 id
----
  4
  5
(2 rows)

All customer ids, minus those that appear on an order, leaves 4 and 5, the same Vuthy and Maly as before. INTERSECT works the other way: SELECT customer_id FROM orders WHERE status = 'paid' INTERSECT SELECT customer_id FROM orders WHERE status = 'cancelled' finds customers who have both a paid and a cancelled order. In our data that is nobody: Sokha and Linda have paid orders, Dara has the cancelled one.

Prefer UNION ALL unless you truly need de-duplication. Removing duplicates means sorting or hashing every row, which costs time.

13. Try It Yourself

All of these use the shopdb data. Predict the answer, then run it.

Write a query using a scalar subquery to list products that cost less than the average price of the Electronics category. (Use a join or a nested subquery to get that average.) How many do you expect?
Using EXISTS, list customers who have at least one order with a status of shipped. Then rewrite it using IN. Do both agree?
Write a CTE called customer_revenue with total spend per customer (excluding cancelled orders). Then select the customer with the highest revenue from it. Which customer is it, and what is the amount?
Using the order_totals view, show each order with rank() over total DESC within each customer_id. Which order is each customer's biggest?
Show every product with its price rank within its category using rank() and PARTITION BY category_id. Then change the query to return only the cheapest product in each category.
Using lag(ordered_at) partitioned by customer, show for each order the date of that customer's previous order. Which orders have a NULL previous date, and why?

Where to go next. You can now ask almost any question of the data. But are the tables themselves designed well? In Normalization and Schema Design you will look at why the schema is split the way it is, learn the normal forms in plain words, and see what goes wrong when one big flat table tries to do everything.