Finding Previous and Next Values: LAG and LEAD

The Month-over-Month Question

Finance asked: *"What was the change in revenue compared to the previous month?"* I needed to access the value from the **previous row**.

`LAG(column, n)`: Gets the value from `n` rows **before** the current row.

`LEAD(column, n)`: Gets the value from `n` rows **after** the current row.

SELECT

month,

revenue,

LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,

revenue - LAG(revenue, 1) OVER (ORDER BY month) AS change

FROM monthly_sales;

The first row has no "previous" row, so `LAG` returns NULL. Use a default value:

LAG(revenue, 1, 0) OVER (ORDER BY month) -- Returns 0 instead of NULL

`LAG` and `LEAD` are essential for time-series analysis: calculating growth rates, deltas, and trend comparisons row by row.

*Day 64: Running Totals and Cumulative Sums.*