Aggregation and window functions
Hook
A change-data-capture feed hands you five rows for the same order: created,
paid, paid again after a retry, shipped, address-corrected. You need exactly one
row — the current state of that order — and you need it for a hundred million
orders tonight. GROUP BY gives you a count or a max, but it throws away the
columns you wanted to keep. The tool that keeps every row while still ranking
them within their group is the window function, and it is the most useful SQL a
data engineer learns after the join.
Concept
GROUP BY aggregation collapses many rows into one per group. Ten order
lines become one revenue total; the individual lines are gone. That is exactly
right when you want the total and nothing else.
A window function computes a value across a set of rows related to the current row — its window — without collapsing anything. Every input row survives in the output, now carrying an extra column: its rank, a running total, the value from the previous row. The difference in one line: aggregation returns one row per group; a window function returns one row per input row.
The shape of a window function is always the same:
function() OVER (PARTITION BY <group> ORDER BY <sort>)
PARTITION BYsplits the rows into groups, likeGROUP BY— but the groups are not collapsed, just scoped.ORDER BYorders the rows inside each partition, which is what makes running totals and rankings meaningful.
Three window functions cover most pipeline work:
| Function | Gives you | Common use |
|---|---|---|
ROW_NUMBER() | A unique 1, 2, 3… within each partition | Deduplicate to one row per key |
RANK() | A ranking that ties and skips (1, 1, 3) | Leaderboards, top-N per group |
SUM() OVER | A running or windowed total | Cumulative revenue, moving averages |
The pattern that earns its keep is deduplication. To keep one row per key,
number the rows within each key by whatever "latest wins" means —
ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC) — then keep only
the rows numbered 1. This is the idiomatic way to collapse a change feed to
current state, and it beats the alternatives (self-joins, correlated subqueries)
on both readability and speed.
Worked example
Let me collapse a change feed to current state, then compute a running total — the two windowing patterns you will reach for most.
Here is a small order_events feed. Order 9001 has three events; the latest
is its true state:
import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE order_events (
order_id INTEGER,
status TEXT,
updated_at TEXT
);
INSERT INTO order_events VALUES
(9001, 'created', '2026-07-01 10:00'),
(9001, 'paid', '2026-07-01 10:05'),
(9001, 'shipped', '2026-07-02 09:00'),
(9002, 'created', '2026-07-01 11:00'),
(9002, 'paid', '2026-07-01 11:30');
""")
To get one row per order, I number each order's events newest-first, then keep
number 1. The numbering happens in a CTE so the outer query can filter on it —
you cannot filter on a window function in the same WHERE clause that computes
it:
current = con.execute("""
WITH ranked AS (
SELECT order_id, status, updated_at,
ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY updated_at DESC
) AS rn
FROM order_events
)
SELECT order_id, status, updated_at
FROM ranked
WHERE rn = 1
ORDER BY order_id
""").fetchall()
for row in current:
print(row)
(9001, 'shipped', '2026-07-02 09:00')
(9002, 'paid', '2026-07-01 11:30')
Order 9001 correctly resolves to shipped and 9002 to paid. The retry and
the earlier states are gone, but — unlike a GROUP BY — every column of the
surviving row came along.
Now a running total, the other everyday window. Given a few days of sales, I want each day's cumulative total to date:
con.executescript("""
CREATE TABLE daily_sales (sales_day TEXT, amount NUMERIC);
INSERT INTO daily_sales VALUES
('2026-07-01', 100),
('2026-07-02', 250),
('2026-07-03', 175);
""")
running = con.execute("""
SELECT sales_day, amount,
SUM(amount) OVER (
ORDER BY sales_day
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM daily_sales
ORDER BY sales_day
""").fetchall()
for row in running:
print(row)
('2026-07-01', 100, 100)
('2026-07-02', 250, 350)
('2026-07-03', 175, 525)
Each row keeps its own amount and gains the cumulative total through that day:
100, then 350, then 525. The ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
frame is what makes it cumulative — it sums from the first row up to this one.
Aggregation could give you the grand total 525; only a window can give you the
total as of each day while keeping the daily rows.
Hands-on
Your turn, on a different feed. The exercise below hands you an inventory_log
with several rows per sku and asks you to produce the current quantity per SKU
by keeping the latest log entry, then to add a running total of units received
per warehouse.
Success criteria: your deduplication returns exactly one row per sku — the
latest by timestamp — and your running total matches the expected cumulative
column. SQL Kingdom validates both result sets automatically.
Recap
- You can state the difference precisely: aggregation returns one row per group; a window function returns one row per input row with an added column.
- You can deduplicate any feed to one row per key with
ROW_NUMBER() OVER (PARTITION BY key ORDER BY … DESC)and arn = 1filter. - You can compute running totals and rankings with
SUM() OVERandRANK()without collapsing the rows they describe.
Next up: what happens when these queries meet a large table. You will read the execution plan to see whether the engine is scanning every row or searching an index — and learn to change the answer.
- Aggregation collapses groups to one row; window functions keep every row and add a scoped column.
ROW_NUMBER() OVER (PARTITION BY key ORDER BY … DESC)withrn = 1is the idiomatic one-row-per-key deduplication.- Filter on a window result in an outer query or CTE, since
WHEREruns before the window is computed.