Chapter 24 · advanced · 8 min
LAG, LEAD & NTILE
You've met ROW_NUMBER, RANK, and running SUM. Three more window functions round out the toolkit interviewers reach for: LAG/LEAD (look at a neighboring row) and NTILE (split rows into equal buckets). Together they answer "how did this change vs. last time" and "which group does this fall into".
We'll analyze day-over-day order volume and customer spending brackets.
The dataset
A `daily_orders` table (one row per day) and a `customer_spend` table (one row per customer).
LAG: look at the previous row
LAG(col) OVER (ORDER BY ...) fetches col's value from the row before the current one, in that ordering:
| order_date | orders | prev_day_orders |
|---|---|---|
| 2026-04-01T00:00:00.000Z | 120 | NULL |
| 2026-04-02T00:00:00.000Z | 135 | 120 |
| 2026-04-03T00:00:00.000Z | 128 | 135 |
| 2026-04-04T00:00:00.000Z | 150 | 128 |
The very first row has no "previous", so its LAG is NULL, that's expected, not an error.
Yesterday's orders, next to today's
Turning LAG into a day-over-day change
Subtract the LAG'd value from the current row and you get the change since last time, the building block of "up 15 orders from yesterday" style reporting.
Day-over-day change
LEAD: look at the next row
LEAD is LAG's mirror image; it looks forward instead of backward. The last row has no "next" row, so its LEAD is NULL.
Tomorrow's orders, next to today's
NTILE: bucket rows into equal groups
NTILE(n) OVER (ORDER BY ...) sorts the rows and splits them into n groups of roughly equal size, numbered 1 to n. With 8 customers and NTILE(4), each quartile gets exactly 2, group 1 is the lowest spenders, group 4 the highest:
| customer | total_spend | quartile |
|---|---|---|
| Devi | 150 | 1 |
| Bala | 300 | 1 |
| Gina | 420 | 2 |
| Ivy | 600 | 2 |
| Farid | 700 | 3 |
| Hakim | 890 | 3 |
| Chong | 950 | 4 |
| Aisyah | 1200 | 4 |
Spending quartiles
If you've used Excel or Google Sheets
LAG/LEAD are the same job as referencing the cell one row up or down (=A2-A1 copied down a column). SQL just makes that relationship explicit and correct even when rows get re-sorted, instead of depending on physical row position. NTILE maps to Excel/Sheets' PERCENTILE/QUARTILE functions, or a PivotTable's "Show Values As → % Running Total" grouping trick, though none of those bucket individual rows into labeled groups quite as directly as NTILE does.
Ready to practice? GrabFood's day-over-day orders question below is the exact LAG pattern from the top of this chapter.
LAG/LEAD and NTILE are the two window functions that come up right after ROW_NUMBER/RANK in a hard interview: period-over-period comparisons and percentile-style bucketing. Two things left: a manual pivot-table pattern SQL doesn't have a keyword for, and a closing chapter on writing all of this so someone else can read it.