How do LAG and LEAD window functions let you access values from a previous or following row without needing a self join?

Intermediate

Answer

LAG retrieves a value from a specified number of rows before the current row within the defined ordering, and LEAD retrieves a value from a specified number of rows after the current row, both letting you easily compare a row to its neighbors, such as calculating the change between consecutive months, without writing a more complex and often slower self join.

SELECT OrderMonth, Revenue,
  LAG(Revenue) OVER (ORDER BY OrderMonth) AS PreviousMonthRevenue,
  Revenue - LAG(Revenue) OVER (ORDER BY OrderMonth) AS MonthOverMonthChange
FROM MonthlyRevenue;

Real-world example A financial dashboard calculates month over month revenue change by comparing each month's revenue to the previous month's using LAG, avoiding the need for a more complex self join to achieve the same comparison.

Common follow-ups: What happens when LAG or LEAD tries to access a row that does not exist, such as before the very first row?;Can you specify a default value to use instead of NULL in these situations?

Data Types & Schema Design;Aggregate Functions & GROUP BY

All 7 Window Functions questions