LAG, LEAD, FIRST_VALUE and LAST_VALUE — SQL & Relational Databases

LAG, LEAD, FIRST_VALUE, and LAST_VALUE are offset window functions — they access a different row's value within the current partition rather than computing an aggregate across all rows. LAG looks backward (the previous row's value), LEAD looks forward (the next row's value), FIRST_VALUE returns the first row's value in the partition, and LAST_VALUE returns the last row's value. These functions are the SQL-native solution for period-over-period comparisons, streak detection, and boundary-value extraction — patterns that appear in virtually every time-series and event-sequence analysis.

Without offset functions, comparing a player's current innings score to their previous innings score required a self-join — joining the innings table to itself on player_id with a condition that selected the previous innings. Self-joins for sequence analysis are verbose, error-prone, and scale poorly because they produce an intermediate Cartesian product that must be filtered down. LAG and LEAD replace self-joins with a single, readable function call that executes in one linear pass over the sorted partition.

For data engineers, these functions are essential for incremental pipeline logic. Detecting whether a record represents a change from its predecessor (LAG), identifying the start and end boundaries of a dimension's validity period (LEAD for effective-to date in SCD Type 2), computing inter-event time deltas for session analysis, and extracting opening and closing values for daily OHLC financial metrics all rely on offset window functions. Mastering their frame specification and NULL handling removes the most complex correlated subquery patterns from pipeline transformation code.

Analogy🏏Cricket

🏏 Think of it like cricket: A SELECT query is precisely how a selection committee picks a playing XI. FROM is the full list of centrally contracted players — the raw pool. WHERE is the fitness and eligibility screen: injured or unavailable players are removed before anyone debates merit, and the fewer names that survive this screen, the faster the meeting goes — exactly why a good WHERE clause matters more than anything downstream. ORDER BY is ranking the survivors by recent form, then by experience as the tiebreaker. LIMIT 11 takes the top of that ranked list and stops. The committee never ranks the entire national player pool and then discards thousands of names — and neither should your query force the database to sort millions of rows it will immediately throw away. The order of operations is the whole game: filter first, sort what remains, take only what you need.