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).
Schema
| order_date | DATE |
| orders | INT |
| customer | TEXT |
| total_spend | NUMERIC |
Example data
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. The very first row has no "previous", so its LAG is NULL, that's expected, not an error.
| order_date | orders | prev_day_orders |
|---|---|---|
| 2026-04-01 | 120 | NULL |
| 2026-04-02 | 135 | 120 |
| 2026-04-03 | 128 | 135 |
| 2026-04-04 | 150 | 128 |
│ frame for the current row (▸): LAG looks exactly one row back — a single offset, not a growing range
Before you run it — what does prev_day_orders show for the very first row, 2026-04-01?
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.
Before you run it — orders went 120, 135, 128, 150. What change_from_prev do you expect on the 2026-04-02 row?
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.
Before you run it — mirror image of LAG. What does next_day_orders show for the last row, 2026-04-04?
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.
Before you run it — Aisyah is the top spender at 1200. Which quartile does she land in, 1 or 4?
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.