Looking at neighbors
LAG and LEAD are window functions that fetch a value from another row relative to the current one, without a self join. LAG reaches backward to an earlier row, while LEAD reaches forward to a later row, based on the OVER ORDER BY.
Arguments
Both accept three arguments:
- The column whose value you want from the neighbor.
- An offset counting how many rows away, defaulting to one.
- A default value returned when no such neighbor exists, such as at the partition edge.
Common uses
LAG shines for period over period comparisons. To compute day over day change, subtract LAG sales from current sales ordered by date. The first row has no previous value, so the default fills the gap. LEAD helps detect the next event, like the gap until a user's next login.
Because PARTITION BY resets the lookup, each group is independent; LAG never bleeds across partition boundaries.
Key idea
LAG and LEAD pull values from earlier or later rows in the ordered window, letting you compare a row to its neighbors without a self join.