Chapter 23 · advanced · 11 min
Window functions
Window functions are the concept that separates intermediate from advanced SQL, and they show up constantly in interviews. The key idea: like an aggregate they look across many rows, but unlike GROUP BY they don't collapse them. Every input row survives, with an extra computed column: its rank, its row number, a running total.
We'll rank customer spending.
The dataset
A sales table: each row is one purchase, customer, region, and amount in ringgit.
Schema
| id | INT |
| customer | TEXT |
| region | TEXT |
| amount | NUMERIC |
Example data
The OVER() clause
What makes a function a window function is OVER(...). It tells SQL "compute this across a window of rows, but keep every row". Here SUM(amount) OVER () puts the grand total next to every individual sale, something a plain GROUP BY can't do while also showing each row.
Before you run it — how many rows come back, and does every row show the same total_all value?
PARTITION BY: windows per group
PARTITION BY splits the rows into groups and restarts the calculation for each, like GROUP BY, but without collapsing. Now each sale sits beside its region's total, and all six rows remain.
Before you run it — North sold 300+300+150. What region_total do you expect on North rows, versus South rows?
ROW_NUMBER: number the rows
ROW_NUMBER() labels rows 1, 2, 3… in the order you give it with ORDER BY inside the OVER. Add PARTITION BY and the numbering restarts per group. This is the standard tool for "the top N per category": number within each partition, then keep number 1.
Before you run it — does numbering restart at 1 for South, or keep counting up from North?
RANK vs ROW_NUMBER: handling ties
They differ only on ties. ROW_NUMBER always gives distinct numbers, breaking ties arbitrarily. RANK gives tied rows the same rank, then skips ahead (1, 2, 2, 4). In the North region, Aisyah and Bala both sold RM 300.
Before you run it — Aisyah and Bala are tied at 300.00 in North. What row_num and rnk do they each get, and what does Chong get next?
DENSE_RANK: ties without the gap
DENSE_RANK is a third option, and it comes up just as often as the other two in interviews. It treats ties the same way RANK does: equal values get equal rank, but it never skips a number afterward. In the South region, Emma and Farid tie at RM 220.
Before you run it — Devi is the top South seller, with Emma and Farid tied behind her. What rnk and dense_rnk do Emma and Farid share, and does the row after them skip a number?
Choosing between the three
| Function | Ties get | Numbers after a tie |
|---|---|---|
ROW_NUMBER | distinct numbers (arbitrary order) | never skip |
RANK | the same number | skip ahead by the tie count |
DENSE_RANK | the same number | never skip |
Pick ROW_NUMBER when you need a strict, unique ordering (like picking exactly one "first" row per group). Pick RANK when you want the skipped number to reflect "how many rows beat this one". Pick DENSE_RANK when you want a clean, contiguous tier number (like "top 3 price tiers", regardless of how many products land in each tier).
Running totals
A window with ORDER BY and no PARTITION BY accumulates as it goes: a running total. Each row's value is the sum of itself and everything ordered before it. This is how you build cumulative revenue over time.
| customer | amount | running_total |
|---|---|---|
| Aisyah | 300.00 | 300.00 |
| Bala | 300.00 | 600.00 |
| Chong | 150.00 | 750.00 |
| Devi | 500.00 | 1250.00 |
| Emma | 220.00 | 1470.00 |
| Farid | 220.00 | 1690.00 |
│ frame for the current row (▸): ORDER BY id, no PARTITION BY — everything from the first row up to this one
Before you run it — ordered by id, sales go 300, 300, 150, 500, 220, 220. What running_total do you expect by the 3rd row?
If you've used Excel or Google Sheets
SUM(amount) OVER (PARTITION BY region) is the same idea as a SUMIF/SUMIFS formula copied down every row: each row computes its group's total without collapsing the sheet. A running total is exactly what you get dragging a =SUM($A$1:A2) formula down a column, where the range's start stays fixed and its end grows each row. RANK() maps closely to Excel/Sheets' own RANK() function. Window functions are really the spreadsheet trick of a formula that sees the whole range while staying on one row, built into the query itself.
Ready to practice? Maybank's rank customers by spend question below is the ROW_NUMBER/RANK pattern from this chapter, one dataset over.
Window functions unlock rankings, top-N-per-group, running totals, and period-over-period comparisons (with LAG/LEAD), the meat of senior data interviews. Take these into the hardest questions in the bank; they're built to drill exactly this.