What are LAG and LEAD?

LAG and LEAD are SQL window functions that allow you to retrieve a value from another row relative to the current one within a specified window (set of rows).

• LAG returns the value from the previous row (shift backwards).
• LEAD returns the value from the next row (shift forwards).

They are useful for comparing the current row with neighboring rows, for example, to calculate differences between values or analyze trends.

Example:

SELECT
  date,
  sales,
  LAG(sales, 1) OVER (ORDER BY date) AS prev_day_sales,
  LEAD(sales, 1) OVER (ORDER BY date) AS next_day_sales
FROM sales_data;