The row before and the row after

"How did this month compare with last month?" needs a value from another row. LAG fetches it from the row before, LEAD from the row after:

WITH monthly AS (
  SELECT date_trunc('month', order_date) AS month, COUNT(*) AS orders
  FROM orders
  WHERE order_date >= '2026-01-01'
  GROUP BY 1
)
SELECT month, orders,
       LAG(orders) OVER (ORDER BY month) AS prev_orders
FROM monthly

January has no month before it in the results, so its prev_orders is NULL.

LAG(orders, 12) reaches back 12 rows: with one row per month, that's the same month last year. LAG(orders, 1, 0) uses 0 instead of NULL when there's no earlier row.

LEAD is the mirror image. With PARTITION BY, both stay within a group:

SELECT customer_id, order_date,
       LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date, id) AS next_order_date
FROM orders

That's each order with the date of the same customer's next one.