What does LAG(amount, 1, 0) OVER (ORDER BY month) return for the first row?
answer
- Reads a neighbouring row's value
- Third argument covers a missing row
- First row of the partition has no predecessor
- Default default is NULL
- LEAD is the same tool pointing forward
basics
~20 sIt returns 0. LAG reads the value one row back in the window's ordering; the first row has no predecessor, so the third argument (the default) is substituted. Omit that argument and you get NULL.
solid answer
~50 s`LAG(expr, offset, default)` returns `expr` evaluated on the row `offset` positions earlier in the window's `ORDER BY` sequence. `offset` defaults to 1 and `default` defaults to NULL. On the first row of a partition there is no row one position back, so the function returns the supplied default — here `0`; with `LAG(amount)` it would return NULL. `LEAD` is the mirror image, looking forward, so it is the *last* row of each partition that falls off the edge. Both are addressed by row position in the ordered partition, so an `ORDER BY` inside `OVER` is essential — without one the "previous" row is not meaningful. Note that supplying `0` as the default is a decision with consequences: a delta of `amount - LAG(amount, 1, 0)` reports the first month's full revenue as growth, which is usually not what you want.
go deeper
Memorise the argument order — expression, offset, default — and be ready to say what the first row returns with and without a default. Know that LEAD is the forward-looking twin.
Explain why the ordering inside OVER is what makes "previous" meaningful, what PARTITION BY changes at the series boundary, and why LAG is unaffected by a frame clause.
Show judgment on the default: NULL versus 0 changes what a growth or ratio column reports for the first period. Be ready to discuss ties in the window ORDER BY making the source row nondeterministic.
Frame it as a modelling choice — whether "no previous period" should surface as NULL, be filtered out, or be materialised as a zero baseline determines what every downstream dashboard shows for the first period of every series.
## What LAG is for `LAG` is an *offset* window function: it lets one row read a value from another row of the same result set, without a self-join. That is its whole purpose. Classic uses are period-over-period comparisons (this month's revenue versus last month's), change detection (did the status column differ from the previous event?), and duration between events (with `LEAD`, the time until the next row). ## The signature ```sql LAG(expression [, offset [, default]]) OVER ([PARTITION BY ...] ORDER BY ...) ``` - `expression` — evaluated on the *target* row, not the current one. It can be any expression over that row's columns. - `offset` — how many rows back to look, counted in the window's ordering. It defaults to `1`. It must be a non-negative integer in the major engines; to look forward, use `LEAD` rather than a negative offset. - `default` — what to return when the target row does not exist. It defaults to NULL. `LEAD` has the identical signature and looks forward instead of back. ## The partition edge Offset functions do not wrap around and do not reach into a neighbouring partition. Within each partition the first `offset` rows have no `LAG` source and the last `offset` rows have no `LEAD` source, so those rows get the default. That is why: ```sql SELECT month, amount, LAG(amount, 1, 0) OVER (ORDER BY month) AS prev_amount FROM monthly_revenue; ``` yields `0` in `prev_amount` for the earliest month and the true previous value for every later month. Written as `LAG(amount)` the same row would show NULL. Which to prefer is a modelling decision, not a style one. NULL is honest: it says "there is no previous period". `0` is convenient for a running sum but lies in a growth calculation, because the first period then appears to have grown from nothing. A safe habit is to leave the default as NULL and let downstream `COALESCE` or a `WHERE` clause decide. ## Ordering and partitioning are what define "previous" "The previous row" only means something relative to the window's `ORDER BY`. Always supply one; some engines require it for `LAG`/`LEAD`, and where it is optional the answer is unspecified without it. If the ordering has ties, which physical row counts as "previous" among the tied rows is not determined — add a tiebreaker column to the window `ORDER BY` when that matters. If the table holds several independent series — one row per region per month, say — add `PARTITION BY region`, or the earliest month of one region will read the last month of another: ```sql LAG(amount) OVER (PARTITION BY region ORDER BY month) ``` ## Frames do not apply Unlike `FIRST_VALUE`, `LAST_VALUE` and windowed aggregates, `LAG` and `LEAD` address rows by position within the whole partition and are not governed by a frame clause. The standard does not allow a frame specification with them. This is a useful asymmetry to remember: a frame you added for a running total has no effect on a `LAG` in the same query. ## NULLs in the source column By default (`RESPECT NULLS`), `LAG` returns whatever sits in the target row, including NULL. It does not skip past NULLs looking for a value. The standard defines an `IGNORE NULLS` option for exactly that, but engine support for it is uneven, so verify before relying on it. ## Where the result can be used The value produced by `LAG` is computed after `WHERE`, `GROUP BY` and `HAVING`, so you cannot filter on it in the same query level. Wrap the query in a CTE or derived table and filter there: ```sql WITH d AS ( SELECT month, amount, amount - LAG(amount) OVER (ORDER BY month) AS delta FROM monthly_revenue ) SELECT * FROM d WHERE delta < 0; ``` ## What interviewers are checking That you know the argument order, that the third argument is the fall-off-the-edge default rather than a NULL replacement for the column, that `LEAD` is the same tool pointed the other way, and that the ordering inside `OVER` is what makes "previous" well defined at all.
- When would you prefer to leave LAG's default as NULL rather than supply 0?Whenever the value feeds a comparison or a ratio. `amount - LAG(amount, 1, 0)` reports the first period's entire amount as growth, and dividing by a defaulted `0` is a division by zero. NULL propagates instead, which correctly marks the first period as "no comparison available". Supply `0` only when you genuinely want a neutral element, such as inside a sum.
- What happens if you omit ORDER BY from the OVER clause of a LAG call?"Previous" stops being defined. Some engines reject the call outright; others accept it and return a value determined by an arbitrary row order, which can change between runs or plans. Always give `LAG` and `LEAD` an explicit window `ORDER BY`, and add a tiebreaker column when the primary ordering key has duplicates.
- How would you compute the number of days until the next order for each customer?Use `LEAD` over the customer's orders: `LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) - order_date`. The most recent order per customer has no following row, so it yields NULL — which is the right answer, since the next order has not happened yet.
LAG is like reading the line above you in a sorted printout: fine everywhere except the top line, where the default is what you write in the margin.
saying these in an interview costs you the question
- Thinks the third argument replaces NULLs in the column
- Believes LAG wraps around to the last row
- Uses a negative offset instead of LEAD
- Omits PARTITION BY and compares across unrelated series
- Assumes a frame clause changes what LAG returns