LAG and LEAD

Reaching other rows

LAG(revenue)  OVER (ORDER BY mth)   -- previous row
LEAD(revenue) OVER (ORDER BY mth)   -- next row
LAG(revenue, 12) OVER (ORDER BY mth) -- twelve rows back
LAG(revenue, 1, 0) OVER (ORDER BY mth) -- default 0 instead of null

The first row has no previous row, so LAG returns null there unless you supply a default.

Month-over-month change

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS mth, SUM(total) AS revenue
  FROM orders GROUP BY 1
)
SELECT mth, revenue,
       revenue - LAG(revenue) OVER (ORDER BY mth) AS change,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY mth))
             / NULLIF(LAG(revenue) OVER (ORDER BY mth), 0), 1) AS pct_change
FROM monthly
ORDER BY mth;

The NULLIF matters — without it, a zero in the prior month raises a division error.

Partitioned comparisons

LAG(revenue) OVER (PARTITION BY region ORDER BY mth)

Each region's sequence is independent, so the first month of each region correctly gets null rather than the last month of the previous region.

Gaps in the data

LAG reaches the previous row, not the previous month. If a month has no orders, that row is simply absent and LAG silently compares across the gap.

Where that matters, join against a generated date series first so every period exists.