LAG() and LEAD() in PostgreSQL: Period-over-Period Analysis Without Self-Joins

The Problem They Solve

"How much did revenue change versus last month?" Answering that with plain SQL traditionally meant joining a table to itself on month = month - 1 — verbose, slow, and error-prone around boundaries. LAG() and LEAD() let each row peek at a sibling row in the same partition without any join.

• LAG(col, n) — value from n rows before the current one (default n = 1).
• LEAD(col, n) — value from n rows after.

Month-over-Month Growth

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS delta,
  round(
    100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
    / NULLIF(LAG(revenue) OVER (ORDER BY month), 0)
  , 1) AS pct_change
FROM monthly_revenue
ORDER BY month;

NULLIF(..., 0) protects against division by zero when the previous month was 0.

Default Values for the Edges

The very first row has no predecessor, so LAG() returns NULL. Supply a third argument as the fallback:

LAG(revenue, 1, 0) OVER (ORDER BY month)  -- first row gets 0 instead of NULL

Detecting Gaps in a Sequence

Find missing invoice numbers — rows where the next number jumps by more than 1:

SELECT invoice_no, next_no
FROM (
  SELECT
    invoice_no,
    LEAD(invoice_no) OVER (ORDER BY invoice_no) AS next_no
  FROM invoices
) s
WHERE next_no - invoice_no > 1;

Time Between Events

Measure the seconds between consecutive logins per user — a building block for sessionization and anomaly detection:

SELECT
  user_id,
  logged_at,
  logged_at - LAG(logged_at) OVER (
    PARTITION BY user_id ORDER BY logged_at
  ) AS gap
FROM logins;

Why It Beats a Self-Join

A self-join re-reads the table and matches rows with a join predicate — quadratic risk if the join columns are not perfectly indexed. LAG()/LEAD() do a single ordered pass (WindowAgg), so they are both faster and harder to get wrong.

From Analysis to Alerting

The "gap between events" pattern above is exactly how PG Monitoring detects silent stalls — a replication slot that stops advancing, a job that quietly stops firing. The same window-function thinking that powers your reports powers production anomaly detection.