Detailed explanation
LAG and LEAD are SQL window functions that read a value from an earlier or later row within an ordered partition, without collapsing the result into grouped totals. LAG looks backward, while LEAD looks forward. They are useful for comparing consecutive sales figures, calculating time between events, detecting status changes, or finding the next appointment. Unlike a self join, they express these neighbouring-row relationships directly while keeping each input row in the output.
The usual syntax is LAG(expression, offset, default) OVER (PARTITION BY ... ORDER BY ...), with the same structure for LEAD. The offset is the number of rows to move and defaults to 1. The default is returned when the requested row does not exist; if omitted, it is NULL. PARTITION BY creates independent groups, such as one sequence per customer or product. Without it, all rows in the window form one partition. ORDER BY defines the sequence used to identify earlier and later rows; it does not guarantee the final display order.
In the example, revenue - LAG(revenue) OVER (ORDER BY month) subtracts the preceding row’s revenue from the current row’s revenue. The first row produces NULL because there is no preceding row, and arithmetic involving NULL returns NULL. This is an absolute revenue change, not a percentage growth rate. It is genuinely month-over-month only when there is exactly one row for every consecutive month. If a month is missing, LAG still reads the previous available row, not the previous calendar month. For separate businesses, add PARTITION BY business_id.
Conceptually, the database evaluates these functions over the rows remaining after filtering and aggregation, using the specified partitioning and ordering. Execution plans may require sorting or buffering, so performance depends on data volume, indexes, and the database engine. Interviewers usually check whether you distinguish row offsets from date intervals, handle boundary NULLs correctly, and provide deterministic ordering. If ordering values can tie, add a meaningful tie-breaker or aggregate to the intended reporting grain first.
Key points to remember
- LAG reads an earlier row; LEAD reads a later row in the window’s ordering.
- Both preserve row-level output and commonly replace self joins for sequence comparisons.
- The offset defaults to 1; an unavailable target row returns the supplied default or NULL.
- PARTITION BY restarts the sequence for each group, while window ORDER BY defines row positions.
- Offsets count rows, not calendar periods; missing periods and duplicate ordering values need explicit handling.
Common mistakes candidates make
- Assuming the previous available row always represents the previous calendar month.
- Omitting required partitioning or using ambiguous ordering when multiple rows share the same month.
- Assuming the default argument replaces NULL values stored in existing target rows; it only handles a missing target row.
Likely follow-up questions
How would you calculate percentage revenue growth?
Calculate LAG(revenue) as previous_revenue in a CTE or subquery, then use 100.0 * (revenue - previous_revenue) / NULLIF(previous_revenue, 0). This avoids division by zero and returns NULL when the previous revenue is unavailable or zero.
Can you filter directly on a LAG result in WHERE?
Generally, no: WHERE is evaluated before window functions. Calculate the result in a CTE or subquery and filter in an outer query, or use QUALIFY where the database supports it.
How do you compare against the previous calendar month when months are missing?
Create a complete calendar-month series and join the sales data to it before applying LAG, deciding whether missing revenue means zero or unknown. Alternatively, join explicitly to the row whose month is exactly one calendar month earlier.
Interview tip: Explain direction, partitioning, and ordering first, then walk through the revenue example and mention its first-row NULL and missing-month caveats.