Subqueries, CTEs & Window Functions
Plain GROUP BY collapses rows. These three techniques answer richer questions without losing row-level detail.
Three tools, one goal — keep row-level detail while asking a harder question:
- Subquery — a query inside a query, in WHERE/FROM/SELECT; correlated subqueries re-run once per outer row.
- CTE (
WITH name AS (...)) — a named subquery, dramatically more readable once a query has more than one step. - Window function — computes a per-row value across a related set of rows, without collapsing them.
- Ranking:
ROW_NUMBER,RANK,DENSE_RANK,NTILE. - Navigation:
LAG/LEAD,FIRST_VALUE/LAST_VALUE.
Subqueries: A Query Inside a Query
A subquery is a complete SELECT nested inside another query, most often in WHERE (to compare against a computed value), FROM (to treat a query's result as if it were a table), or SELECT (to compute one extra column per row). A plain subquery runs once, independently, and its result is reused for every row of the outer query. A correlated subquery is different: it references a column from the outer query, so the engine re-runs it once per outer row — powerful (each row can ask its own question), but potentially slow if the table is large, since it's effectively a query-per-row instead of one query total.
SQL · a plain subquery vs. a correlated subquery
-- Plain subquery in WHERE: runs ONCE, the outer query compares against that one number
SELECT c.name, SUM(oi.quantity * oi.unit_price) AS total_spend
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY c.id, c.name
HAVING SUM(oi.quantity * oi.unit_price) > (
SELECT AVG(customer_total) FROM (
SELECT SUM(oi2.quantity * oi2.unit_price) AS customer_total
FROM orders o2 JOIN order_items oi2 ON oi2.order_id = o2.id
GROUP BY o2.customer_id
)
);
-- Correlated subquery: re-runs once per product, because it references p.id from the outer row
SELECT p.name,
(SELECT MAX(o.order_date)
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE oi.product_id = p.id) AS last_ordered
FROM products p;CTEs Over Nested Subqueries
A CTE (Common Table Expression, written WITH name AS (...)) is a subquery given a name up front, so the rest of the query can reference it just like a real table. It computes exactly the same result a nested subquery would, but reads top to bottom in the order you'd explain it out loud, instead of inside-out the way deeply nested subqueries do. Chained CTEs (a later one referencing an earlier one) turn a multi-step calculation — order revenue, then rank customers by it — into something a reader can follow in order, one labeled step at a time, rather than untangling several levels of parentheses.
SQL · chained CTEs
WITH order_revenue AS (
SELECT o.id AS order_id, o.customer_id,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, o.customer_id
),
customer_revenue AS (
SELECT customer_id, SUM(revenue) AS total_revenue
FROM order_revenue
GROUP BY customer_id
)
SELECT c.name, cr.total_revenue
FROM customer_revenue cr
JOIN customers c ON c.id = cr.customer_id
ORDER BY cr.total_revenue DESC
LIMIT 5;The Window Function Anatomy
A window function computes a value for each row by looking at a set of related rows — its "window" — without collapsing them the way GROUP BY does. Every window function shares the same anatomy: FUNCTION() OVER (PARTITION BY col ORDER BY col ROWS BETWEEN ...). PARTITION BY is optional and restarts the calculation for each group (rank salaries within each department, not across the whole company); ORDER BY defines the row order the calculation walks through (needed for anything cumulative, like a running total or a rank); the frame clause (ROWS BETWEEN ...) narrows the window further to just a few rows around the current one (needed for a moving average). The functions themselves fall into three families: ranking (ROW_NUMBER, RANK, DENSE_RANK, NTILE), navigation (LAG/LEAD look at a neighboring row; FIRST_VALUE/LAST_VALUE look at the window's edges), and plain aggregates run as a window (SUM/AVG/COUNT OVER (...) — the same functions from Part 3, just no longer collapsing rows).
SQL · RANK() vs. DENSE_RANK()
SELECT name, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rnk
FROM employees
WHERE department = 'Sales'
ORDER BY salary DESC;RANK() leaves a gap after a tie (1, 1, 3, 4); DENSE_RANK() doesn't (1, 1, 2, 3).
The LAST_VALUE Gotcha
Every window query has an implicit frame even when you never type one, and that default is the single most common source of a wrong-looking running total or a broken LAST_VALUE. The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — "everything from the start of the partition up to and including this row" — which is exactly what you want for a running total (each row adds to everything before it), but is exactly wrong for LAST_VALUE: since the frame always stops at the current row, LAST_VALUE ends up returning the current row's value on every row, never the true last row of the partition. Always specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING explicitly when you actually want the last row in the whole window, not just "as far as we've gotten so far."
SQL · running total + LAG
-- Running total: SUM() OVER an ORDER BY, no PARTITION BY
SELECT o.order_date,
SUM(oi.quantity * oi.unit_price) AS daily_revenue,
SUM(SUM(oi.quantity * oi.unit_price)) OVER (ORDER BY o.order_date) AS running_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.order_date
ORDER BY o.order_date;
-- LAG: this customer's previous order total, for month-over-month comparison
SELECT c.name, o.order_date,
SUM(oi.quantity * oi.unit_price) AS order_total,
LAG(SUM(oi.quantity * oi.unit_price)) OVER (
PARTITION BY c.id ORDER BY o.order_date
) AS previous_order_total
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
WHERE c.id = 12
GROUP BY c.name, o.id, o.order_date;Window Functions Run Late
SQL clauses have a logical execution order that doesn't match the order you type them in: FROM → WHERE → GROUP BY → HAVING → window functions → SELECT → ORDER BY → LIMIT. Window functions sit right before the final SELECT, which means they run after WHERE/GROUP BY/HAVING have already trimmed the rows down, but the query's WHERE clause has no way to filter on a window function's own result — it hasn't been computed yet at the point WHERE runs. The fix is always the same: compute the window function inside a CTE or subquery first, then filter the CTE's output in a second, outer SELECT.
SQL · filtering a window function's result
-- WRONG: can't reference a window alias in the same query's WHERE
-- SELECT name, RANK() OVER (...) AS rnk FROM employees WHERE rnk = 1; -- error
-- RIGHT: wrap it in a CTE, then filter the CTE
WITH ranked AS (
SELECT name, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
FROM employees
)
SELECT * FROM ranked WHERE rnk = 1;Each step keeps every row visible while adding more context to it.
The takeaway: A CTE and a window function solve different problems — a CTE structures multi-step logic; a window function computes a value per row without collapsing the result set. Most real analytics queries use both together.
Runnable code for this part: sql-for-data-platforms on GitHub — Module 4 of 14.
← Back to Publications