Week 4: Subqueries, CTEs & Window Functions

1. Subqueries in WHERE

-- customers whose average order total is above the overall average
SELECT name FROM customers
WHERE id IN (
    SELECT customer_id FROM orders
    GROUP BY customer_id
    HAVING AVG(total) > (SELECT AVG(total) FROM orders)
);

The inner query runs first (conceptually), producing a set of values the outer query's WHERE ... IN (...) then filters against. This particular query nests two levels deep — an aggregate subquery for the overall average, inside a grouped subquery for per-customer averages, inside the outer filter — which is exactly the kind of query that gets hard to read past two or three levels, and exactly what CTEs (next) are for.

2. Subqueries in FROM & CTEs

SELECT customer_totals.name, customer_totals.total_spent
FROM (
    SELECT customers.name, SUM(orders.total) AS total_spent
    FROM customers
    JOIN orders ON customers.id = orders.customer_id
    GROUP BY customers.name
) AS customer_totals
WHERE customer_totals.total_spent > 100;

WITH customer_totals AS (
    SELECT customers.name, SUM(orders.total) AS total_spent
    FROM customers
    JOIN orders ON customers.id = orders.customer_id
    GROUP BY customers.name
)
SELECT name, total_spent
FROM customer_totals
WHERE total_spent > 100;

A CTE (Common Table Expression), introduced with WITH, is functionally identical to a subquery in FROM — the difference is purely readability: it's named, appears before the main query instead of nested inside it, and multiple CTEs can chain together, each referencing the ones before it, which reads top-to-bottom instead of inside-out.

3. Window Functions: ROW_NUMBER & RANK

A window function computes a value across a set of related rows — like an aggregate — but without collapsing them into one row per group the way GROUP BY does. Every original row stays in the result, with the computed value attached.

SELECT
    customer_id,
    id AS order_id,
    total,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY id DESC) AS order_rank
FROM orders;

PARTITION BY is GROUP BY's window-function equivalent — it splits rows into groups for the function to operate within — but unlike GROUP BY, every row from every partition still appears in the output. RANK() works the same way as ROW_NUMBER() but assigns the same rank to tied rows (and skips the next number), where ROW_NUMBER() always assigns a strictly increasing, unique number regardless of ties.

4. Running Totals & Moving Averages

SELECT
    created_at,
    total,
    SUM(total) OVER (ORDER BY created_at) AS running_total
FROM orders
ORDER BY created_at;

With no PARTITION BY, the window spans the whole result set — each row's running_total is the sum of every total up to and including that row's position in the ORDER BY. Aggregate functions (SUM, AVG, COUNT) become window functions simply by adding OVER (...) — the same functions from Week 2, used differently.

5. Hands-on Exercise

6. Knowledge Check

Four quick questions. Expand each to check your answer.