Subqueries and window functions — counting across rows

"For each customer, list the two largest-amount orders". This requirement can't be written with GROUP BY. Aggregating folds to one row per customer, erasing individual orders. But without folding, there's no way to rank.

You need tools that keep rows while looking at other rows for computation. SQL has two systems for that: subqueries and window functions.

What you'll learn in this chapter

• Three positions subqueries fit, and each role
• Correlated subqueries, EXISTS, and the difference from NOT IN
• Arranging processing in reading order with CTE (WITH)
• How window functions aggregate without folding rows
• Differences between ROW_NUMBER, RANK, DENSE_RANK
• Running totals and top-N per group
• Which databases and versions support them

Prerequisites

Builds on evaluation order, GROUP BY granularity, and NULL three-valued logic from Joins and aggregation. Uses the same example tables (customers 5 rows / orders 7 rows / coupons 3 rows).

What this chapter does not cover

Topic	See
Choosing writing style by looking at execution plans	Database Design Guide — Introduction to index design
Traversing hierarchical data with recursive CTEs	Not covered here (tree structure itself in Choosing data structures)
Join basics and aggregation granularity	Joins and aggregation

Three positions subqueries fit

A subquery is a SELECT inside a SELECT. Where you put it changes the role.

In SELECT clause, it becomes a one-row one-column value (scalar subquery). Must return one value per outer row.

SELECT c.name,
       (SELECT COUNT(*) FROM orders AS o WHERE o.customer_id = c.id) AS orders
FROM customers AS c
ORDER BY c.id;

All 5 customers return as 5 rows, and Takahashi shows 0. No join, so rows don't increase. Same result as outer join with COUNT, but this one visibly leaves row count unchanged.

In FROM clause, it becomes a table (derived table). Used to further filter aggregated results.

SELECT t.customer_id, t.total
FROM (
  SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id
) AS t
WHERE t.total >= 5000
ORDER BY t.total DESC, t.customer_id;

3 rows (customers 1 and 5 with 9500; customer 2 with 7000) return. Same result via HAVING, but for stacking joins or computations on aggregated results, derived tables are more natural.

In WHERE clause, it becomes part of the condition.

SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE amount >= 5000)
ORDER BY id;

3 people (Sato, Suzuki, Ito) return.

Correlated subqueries and EXISTS

Of the three above, only the first references an outer column (c.id). This is called a correlated subquery. Semantically, it evaluates per outer row. Actual execution is decided by the execution plan, so speed can't be judged from syntax.

The staple for conditions is EXISTS.

SELECT c.name FROM customers AS c
WHERE EXISTS (
  SELECT 1 FROM orders AS o WHERE o.customer_id = c.id AND o.amount >= 5000
)
ORDER BY c.id;

Same 3 people as IN above. EXISTS only sees whether rows return. PostgreSQL explains "The subquery will generally only be executed long enough to determine whether at least one row is returned" (PostgreSQL — Subquery Expressions). SELECT 1 is written for that reason — column content isn't used.

Negation reveals the difference between IN and EXISTS. As seen in Joins and aggregation, NOT IN with NULL in the right doesn't become true and returns zero rows. NOT EXISTS only sees row existence and isn't affected.

-- AUTUMN returns. The NOT IN version was 0 rows
SELECT p.id, p.code FROM coupons AS p
WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.coupon_id = p.id);

Making NOT EXISTS the default for negation reduces accidents.

Reading order with CTE

Nested subqueries mean reading inside-out. When processing order and reading order reverse, tracking becomes lost past 3 levels.

WITH names intermediate results and lets you arrange top-down. SQLite explains it as "acts like a temporary view that lasts for the duration of a single SQL statement" (SQLite — WITH clause).

WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id
)
SELECT c.name, t.total
FROM customer_totals AS t
JOIN customers AS c ON c.id = t.customer_id
WHERE t.total >= 5000
ORDER BY t.total DESC, c.id;

Sato 9500, Ito 9500, Suzuki 7000 return. Same result as the derived-table version. With a name, readers don't have to infer "what does this intermediate represent". Multiple CTEs separated by commas allow staged writing.

Window functions don't fold rows

The problem with GROUP BY is that aggregating erases original rows. Window functions produce aggregates while keeping rows. PostgreSQL's explanation is succinct:

A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. However, window functions do not cause rows to become grouped into a single output row like non-window aggregate calls would. Instead, the rows retain their separate identities.

— Summary of PostgreSQL — Window Functions

SELECT c.name, o.ordered_on, o.amount,
       SUM(o.amount) OVER (PARTITION BY o.customer_id) AS customer_total
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
ORDER BY o.id;

All 7 orders return as 7 rows, with each row carrying that customer's total. Sato's 3 rows all show 9500. With GROUP BY customer_id, it would fold to 4 rows, losing each order's date and amount.

The content of OVER decides "which rows to look at". PARTITION BY is the aggregation unit, corresponding to GROUP BY. Empty OVER () covers all rows.

Window functions can only be written in SELECT and ORDER BY clauses. Not in WHERE, GROUP BY, or HAVING — the reason is "they logically execute after those processes" (same source). Writing WHERE ROW_NUMBER() OVER (...) <= 2 gives SQLite misuse of window function ROW_NUMBER(). How to filter by rank is below.

ROW_NUMBER, RANK, DENSE_RANK

Differences show with ties.

WITH totals AS (
  SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id
)
SELECT c.name, t.total,
       ROW_NUMBER() OVER (ORDER BY t.total DESC) AS rn,
       RANK()       OVER (ORDER BY t.total DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY t.total DESC) AS dense_rnk
FROM totals AS t
JOIN customers AS c ON c.id = t.customer_id
ORDER BY t.total DESC, c.id;

name	total	rn	rnk	dense_rnk
Sato	9500	1	1	1
Ito	9500	2	1	1
Suzuki	7000	3	3	2
Tanaka	1200	4	4	3

• ROW_NUMBER assigns sequential numbers from 1. Ties don't get the same number
• RANK gives ties the same rank, skipping ahead by that many (1, 1, 3)
• DENSE_RANK gives ties the same rank without skipping (1, 1, 2)

Source: SQLite — Window Functions.

Which tied row ROW_NUMBER assigns 1 to isn't determined. Sato getting 1 and Ito 2 above happened by chance. To make it reproducible, add tie-breaking columns to OVER's ORDER BY for uniqueness.

Running totals

Writing ORDER BY in OVER changes the aggregation target from all rows to just the leading portion. But where it's cut is decided by the frame, and the default frame has a pitfall below. First, a uniquely ordered example.

SELECT ordered_on, amount,
       SUM(amount) OVER (ORDER BY ordered_on, id) AS running_total
FROM orders
ORDER BY ordered_on, id;

7 rows by date, with running_total going 3000 → 8000 → 13000 → 14200 → 23700 → 25700 → 27200. Adding PARTITION BY customer_id gives per-customer running totals, restarting on customer change (Sato: 3000 → 8000 → 9500).

Adding , id to ORDER BY prevents the running total from breaking if same-date orders exist. This example's data has all unique dates, so removing it wouldn't change the result. The break condition: the default frame is RANGE UNBOUNDED PRECEDING, meaning "from the beginning through the last row with the same ordering value as the current row (peer)" (PostgreSQL — Window Function Calls). Rows with the same ordering share the frame, so all get the same total.

Executing SUM(amount) OVER (ORDER BY customer_id) locally, Sato's 3 rows all showed 9500. Not accumulating row by row. For running totals on columns that can have equal values, either make the ordering unique or explicitly frame as ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Top-N per group

The requirement at the start. Window functions can't go in WHERE, so wrap the ranked result in a CTE or derived table, and filter outside.

WITH ranked AS (
  SELECT o.customer_id, o.ordered_on, o.amount,
         ROW_NUMBER() OVER (
           PARTITION BY o.customer_id ORDER BY o.amount DESC, o.id
         ) AS pos
  FROM orders AS o
)
SELECT c.name, r.ordered_on, r.amount, r.pos
FROM ranked AS r
JOIN customers AS c ON c.id = r.customer_id
WHERE r.pos <= 2
ORDER BY c.id, r.pos;

Sato has 2 rows (5000 and 3000), Suzuki 2 (5000 and 2000), Tanaka and Ito 1 each — 6 rows total. Only Sato's 1500 (the third-largest of three) is dropped.

Adding , o.id as second column in ORDER BY o.amount DESC, o.id ensures rank is determined even on ties. Without it, which tied row is picked can vary per execution.

Which writing to choose

Multiple ways can produce the same result. Which is faster can't be decided from writing alone. The execution plan depends on data distribution and indexes, so compare with EXPLAIN (Database Design Guide — Introduction to index design). What can be judged from writing is meaning and readability.

Want to	Choose	Reason
See whether a row satisfying condition exists	EXISTS / NOT EXISTS	Rows don't increase. Doesn't go empty on NULL the way NOT IN does
Need the column value of the partner table	Join	A scalar subquery returns only one column per call
Further process aggregated results	CTE or derived table	Intermediate has a name; readable top-down
Produce aggregate values but keep original rows	Window function	GROUP BY erases rows
Top-N per group	Window function + CTE	Ranking and filtering can't be in the same layer

Don't forget joins increase rows. "Customers with an order ≥ 5000" written with a join gives 2 rows for customers with 2 such orders. Before removing with DISTINCT, see if it can be written with EXISTS.

Verify availability first

Both are relatively new features that don't work in old environments.

Database	Window function	CTE (WITH)
PostgreSQL	8.4 (2009-07-01)	8.4 (2009-07-01)
MySQL	8.0.2 (2017-07-17)	8.0.1 (2017-04-10)
SQLite	3.25.0 (2018-09-15)	3.8.3 (2014-02-03)

Sources: PostgreSQL 8.4 release notes, MySQL 8.0.1 release notes, MySQL 8.0.2 release notes, SQLite — Window Functions, SQLite 3.8.3 release notes.

Both MySQL versions are development milestone releases, which do not mean what the other rows' versions mean. The release notes drop the milestone and release-candidate labels from 8.0.11 (2018-04-19).

Even for the same feature, fine-grained syntax support arrives later. SQLite gained window range specification (EXCLUDE, GROUPS, RANGE boundary by expression) in 3.28.0 (2019-04-16). The basic PARTITION BY and ORDER BY came in 3.25.0 (2018-09-15) — different versions. MySQL's LIMIT inside recursive CTE is available from 8.0.19. Verify version conditions per syntax, not per feature.

Environments on MySQL 5.7 or earlier can't write either. If migration is planned, splitting the design around window functions reduces later rewrites.

Common misconceptions

"Window functions are a substitute for GROUP BY." — Opposite purpose. GROUP BY folds rows; window functions don't. For one row per customer, GROUP BY suffices.

"You can filter by rank in WHERE." — Window functions only in SELECT and ORDER BY. To filter by rank, wrap in CTE or derived table.

"RANK and ROW_NUMBER are nearly the same." — Result changes with ties. Taking top 3 with RANK, if there are 4 first-place, gives 4 rows; ROW_NUMBER always gives 3.

"ROW_NUMBER's ranking is deterministic." — If OVER's ORDER BY doesn't break ties, which row comes first isn't determined. Add columns for uniqueness.

"OVER with ORDER BY gives running totals." — The default frame is up to the last peer, so rows with the same ordering share a total. Make ordering unique, or explicitly frame with ROWS.

"CTE is faster / slower." — Generally undetermined. But PostgreSQL 12+ inlines side-effect-free, non-recursive, single-reference CTEs into the outer query, computing multiple-reference CTEs once. MATERIALIZED blocks inlining; NOT MATERIALIZED forces it. Before 12, no inlining occurred — always evaluated first (PostgreSQL 12 release notes). Whether it makes a difference depends on version and reference count, so use EXPLAIN to compare.

"Joins are faster than subqueries." — Same as above. But joins can increase rows, so meaning-wise you need to differentiate with EXISTS.

Exercises

Q1. SELECT customer_id, amount, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS pos FROM orders WHERE pos = 1 errors. Explain and give the correct form.

Answer and explanation

Two reasons, both from evaluation order.

First, pos is an alias set in SELECT. WHERE runs before SELECT, so pos doesn't exist then (a constraint seen in Joins and aggregation). But this applies to standard SQL and PostgreSQL / MySQL; SQLite allows aliases in WHERE.

Second, even without alias, writing ROW_NUMBER() OVER (...) directly in WHERE doesn't work. Window functions only go in SELECT and ORDER BY, because they execute after WHERE.

The correct form wraps and filters.

WITH ranked AS (
  SELECT customer_id, amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC, id) AS pos
  FROM orders
)
SELECT customer_id, amount FROM ranked WHERE pos = 1;

Adding id to ORDER BY makes rank unique when amounts tie. This query returns 4 rows (customer 1 with 5000, 2 with 5000, 3 with 1200, 5 with 9500).

Note the error actually raised in SQLite is only the second (misuse of aliased window function pos); as the message names the alias pos, the first doesn't stop it.

Q2. Two customers total 9500, one totals 7000, one totals 1200. What do RANK and DENSE_RANK give in descending amount?

Answer and explanation

RANK gives 1, 1, 3, 4; DENSE_RANK gives 1, 1, 2, 3.

Both give ties the same rank. The difference is next-rank decision. RANK sees "how many rows came before me", so if two are 1st, next is 3rd. DENSE_RANK increments the rank value by 1 without skipping.

Behavior differs for "display top 3": RANK <= 3 gives 3 rows (two 9500s and one 7000); DENSE_RANK <= 3 gives 4 rows (all). Decide how to treat ties, then choose the function.

Q3. For "customers who never used a coupon", why recommend NOT EXISTS over NOT IN? Write both queries and explain.

Answer and explanation

Because NOT IN with any NULL in the right returns zero rows.

-- The moment any NULL mixes in, result is always empty
SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders WHERE coupon_id IS NOT NULL);

-- Unaffected by NULL presence
SELECT c.name FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1 FROM orders AS o WHERE o.customer_id = c.id AND o.coupon_id IS NOT NULL
);

Both return Suzuki, Takahashi, Ito. In this example, orders.customer_id has NOT NULL, so the above works now. The problem is that it depends on the constraint. The moment the column definition changes or an outer join enters and NULL mixes in, it silently returns empty results without error.

NOT EXISTS only sees whether matching rows exist and doesn't break this way. Make NOT EXISTS the default for negations.

Summary

• Subqueries change role by position. In SELECT a value, in FROM a table, in WHERE part of a condition
• EXISTS only sees row presence. Prefer NOT EXISTS over NOT IN for negations
• CTE names intermediate results and enables top-down reading. Same result as derived tables, but execution differs by version and reference count (PostgreSQL 12+ inlines single-reference CTEs)
• Window functions produce aggregates without folding rows. Opposite purpose from GROUP BY
• Window functions only in SELECT and ORDER BY. To filter by rank, wrap in CTE or derived table
• ROW_NUMBER is sequential, RANK is rank with gaps, DENSE_RANK is rank without gaps. For uniqueness on ties, add columns to OVER's ORDER BY
• Default frame is "up to the current row's last peer", so tied ordering gives all rows the same running total. Make ordering unique, or explicitly frame with ROWS
• Speed comparisons of equivalent writings can't be decided from writing alone. Pick by meaning and readability; verify speed with EXPLAIN
• Supported versions: PostgreSQL 8.4 / MySQL 8.0 / SQLite 3.25.0 (CTE from 3.8.3). Syntax details land in later versions

Related references

• Database Design Guide — Introduction to index design — EXPLAIN reading and which columns to index
• PostgreSQL — Window Functions — Definition and where they can be written
• PostgreSQL — Subquery Expressions — Meaning of EXISTS and IN
• SQLite — Window Functions — Ranking function definitions and supported versions
• MySQL — WITH (Common Table Expressions) — CTE and recursive CTE syntax

What to read next

• Event loop — On to Part 13. From data read/write to how processing itself lines up
• Indexes — The side that decides whether written queries run fast
• Data distribution — What happens to joins and aggregation when tables don't fit on one machine
• Locks and isolation levels — How it looks when other updates arrive mid-aggregation