Compare a Metric Year-Over-Year Using LAG

Pull the prior year's value into the current row with `LAG`, then compute the percentage change — a common pattern for revenue, signups, and other business KPIs.

Created May 5, 2026 Last updated May 5, 2026 0/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Compute year-over-year revenue growth for three consecutive fiscal years

The CTE computes LAG(revenue) once and names the result prev_revenue, keeping the outer query readable. The 2022 row has no prior year so prev_revenue and yoy_growth_pct are both NULL. For 2023: (1200 − 1000) × 100.0 / 1000 = 20.0%. For 2024: (1500 − 1200) × 100.0 / 1200 = 25.0%. Using 100.0 instead of 100 forces decimal division so the result is never truncated to an integer. ROUND(..., 1) limits the output to one decimal place. To handle the NULL first row gracefully in a report, wrap yoy_growth_pct in COALESCE(yoy_growth_pct, 0) or display it as 'N/A' via a CASE expression.

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE annual_revenue (fiscal_year INT, revenue INT);

INSERT INTO
  annual_revenue
VALUES
  (2022, 1000),
  (2023, 1200),
  (2024, 1500);

Validated query Shared across supported engines.

SQL

WITH
  prev AS (
    SELECT
      fiscal_year,
      revenue,
      LAG (revenue) OVER (
        ORDER BY
          fiscal_year
      ) AS prev_revenue
    FROM
      annual_revenue
  )
SELECT
  fiscal_year,
  revenue,
  prev_revenue,
  ROUND(
    (revenue - prev_revenue) * 100.0 / prev_revenue,
    1
  ) AS yoy_growth_pct
FROM
  prev
ORDER BY
  fiscal_year;

Expected result Returned rows for the shared example.

fiscal_year	revenue	prev_revenue	yoy_growth_pct
2022	1000	NULL	NULL
2023	1200	1000	20
2024	1500	1200	25

Output is identical across all supported engine versions.

Useful when

Where this command helps.

showing annual revenue growth percentage alongside raw revenue in a finance report
comparing this year's monthly signups to the same month last year in a growth dashboard

Explanation

What the command is doing.

LAG(column) OVER (ORDER BY period) retrieves the value from the previous row in the ordered sequence. When the sequence is yearly (or monthly), this gives direct access to the prior period's figure without a self-join. Wrapping the query in a CTE isolates the lag computation so the percentage change formula (current − prior) * 100.0 / prior can reference prev_revenue by name. Using 100.0 rather than 100 forces floating-point division in all engines, preventing integer truncation. ROUND(..., 1) trims the percentage to one decimal place. The first period always returns NULL for the prior value and the growth percentage because no earlier row exists. To compare the same month across years — rather than year-to-year totals — add PARTITION BY EXTRACT(month FROM period_date) inside the OVER clause so LAG only looks back within the same calendar month.

Compare a Metric Year-Over-Year Using LAG

Compute year-over-year revenue growth for three consecutive fiscal years

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.