Year-Over-Year Revenue Growth

The Problem

Management wants a report showing monthly revenue alongside the percentage change compared to the same month in the previous year. This is a standard financial KPI -- it removes seasonality from the comparison (unlike month-over-month) and gives a clear picture of growth or decline.

Every investor deck, board report, and earnings call references YoY numbers. Analysts build these queries constantly.

The Pattern: Comparing Rows Across Time Periods

This question tests two approaches to the same problem:

Approach 1: Self-join on offset keys. Join the table to itself where the month matches and the year is offset by 1. This is explicit and works in all SQL dialects.

Approach 2: LAG/LEAD window functions. Use LAG(revenue, 12) if data is dense (every month present), or partition by month and use LAG(revenue, 1) ordered by year.

The self-join is more robust when data has gaps (missing months). The window function is more concise when data is dense.

This pattern applies in any time-series comparison:

Same-store sales growth in retail (comparing each location to its own prior year)
Website traffic YoY -- comparing January this year to January last year
Manufacturing output -- this quarter vs. same quarter prior year
Customer count growth by cohort month
Utility usage comparisons (heating costs this winter vs. last winter)
Marketing spend efficiency -- cost per acquisition this month vs. same month prior year

The key insight is that "same period, different year" is a join condition, not a window frame.

The Setup

DROP TABLE IF EXISTS monthly_revenue;
CREATE TABLE monthly_revenue (
    year INT,
    month INT,
    revenue DECIMAL(12,2),
    PRIMARY KEY (year, month)
);

INSERT INTO monthly_revenue (year, month, revenue) VALUES
(2022, 1, 50000), (2022, 2, 55000), (2022, 3, 48000),
(2022, 4, 62000), (2022, 5, 58000), (2022, 6, 70000),
(2023, 1, 56000), (2023, 2, 52000), (2023, 3, 55000),
(2023, 4, 68000), (2023, 5, 61000), (2023, 6, 75000),
(2024, 1, 63000), (2024, 2, 59000), (2024, 3, 61000);

Note that 2022 has no prior year data, so those rows should show NULL for the comparison. February 2023 shows a decline vs. February 2022 (52k vs 55k) -- your query should handle both growth and decline.

The Challenge

For each month, show the revenue, the prior year's revenue for the same month, and the year-over-year percentage change rounded to 1 decimal place. Months without a prior year comparison should still appear with NULL for the change.

Try your solution here

Discuss on Discord