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.
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:
| customer | amount | total_all |
|---|---|---|
| Aisyah | 300.00 | 1690.00 |
| Bala | 300.00 | 1690.00 |
| Chong | 150.00 | 1690.00 |
| Devi | 500.00 | 1690.00 |
| Emma | 220.00 | 1690.00 |
| Farid | 220.00 | 1690.00 |
Every row keeps its detail + the total
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:
| customer | region | amount | region_total |
|---|---|---|---|
| Aisyah | North | 300.00 | 750.00 |
| Bala | North | 300.00 | 750.00 |
| Chong | North | 150.00 | 750.00 |
| Devi | South | 500.00 | 940.00 |
| Emma | South | 220.00 | 940.00 |
| Farid | South | 220.00 | 940.00 |
Total per region, rows intact
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:
| customer | region | amount | rank_in_region |
|---|---|---|---|
| Aisyah | North | 300.00 | 1 |
| Bala | North | 300.00 | 2 |
| Chong | North | 150.00 | 3 |
| Devi | South | 500.00 | 1 |
| Emma | South | 220.00 | 2 |
| Farid | South | 220.00 | 3 |
This is the standard tool for "the top N per category": number within each partition, then keep number 1.
Rank customers within their region
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: ROW_NUMBER splits them into 1 and 2 (arbitrarily), while RANK gives both rank 1, then jumps Chong straight to rank 3.
Compare the two on tied amounts
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: RANK jumps from 1 straight to 3, while DENSE_RANK continues at 2.
South region: RANK skips to 3, DENSE_RANK doesn't
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.
Cumulative sum row by 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.