Year-over-Year Growth Analysis in SQL

"How much did we grow compared to last year?"

This is one of the most common questions in business intelligence. To answer it, you need to access data from the previous row while looking at the current row.

Enter LAG(), the time-traveler of SQL functions.

A Real Business Scenario: Finance Needs Comparable Periods, Not Just Bigger Numbers

A revenue dashboard often looks impressive when the current period is bigger than the last one. That still does not answer the finance question.

What the team usually needs is:

• the current period value
• the comparable prior-period baseline
• the absolute change
• the percentage change

That sounds simple, but it only works if the comparison periods are aligned correctly. Comparing 2024 Q1 to the previous row is only valid when the previous row is actually 2023 Q1, not some other time bucket that happened to sort nearby.

Year-over-year SQL is useful because it forces you to represent the baseline explicitly instead of relying on visual guesses from a chart.

The LAG() Function

LAG() lets you peek at a previous row.

LAG(column_name, offset, default_value) OVER (ORDER BY sort_column)

• column_name: The value you want to retrieve (e.g., revenue).
• offset: How many rows back? (Default is 1).
• default_value: What to return if there is no previous row? (Default is NULL).

Scenario: Annual Revenue Growth

Let's say we have a table of yearly revenue. We want to calculate:

1. Previous Year's Revenue
2. Growth Amount (Current - Previous)
3. Growth Percentage ((Current - Previous) / Previous * 100)

A Common Mistake: Using the Previous Row as If It Were Always "Last Year"

LAG() returns the previous row in the chosen order. It does not know what "year-over-year" means by itself.

That becomes dangerous when data is missing periods or when rows are not at the expected grain.

For example, if your dataset skips 2021, then:

LAG(revenue) OVER (ORDER BY year)

on the 2022 row will compare against 2020, not 2021. The query still runs, but the label "YoY growth" becomes wrong.

This is why period-over-period analysis usually needs one of these safeguards:

• fill missing periods first
• join to a calendar table
• aggregate to one row per year or one row per month before applying LAG()

1. The Raw Data

2. The Growth Calculation

Now, let's use LAG() to bring the 2020 revenue onto the 2021 row.

Analysis:

• 2020: prev_year_revenue is NULL because there is no 2019 data. Consequently, growth is NULL.
• 2021: Grew from 100k to 120k. +20,000 (+20%).
• 2022: Dropped from 120k to 110k. -10,000 (-8.33%).

Handling NULLs with Default Values

If you want the first year to show 0 growth instead of NULL, you can set a default value in LAG (like 0), or handle the NULL result in your math. However, showing NULL for the first period is usually mathematically correct (growth is undefined without a baseline).

Month-over-Month (MoM)

The same logic applies to months!

LAG(revenue) OVER (ORDER BY year, month)

Just ensure your data has one row per month. If you have missing months (gaps), comparing Row N to Row N-1 might compare March to January! Correcting for gaps typically involves generating a date series (using a recursive CTE) to fill in missing months with 0 before using LAG.

LEAD(): Looking into the Future

The opposite of LAG() is LEAD(). It lets you peek at next year's revenue. This is useful for building models where features include "future target values".

Boundary and Performance Notes

Year-over-year analysis is usually lightweight after aggregation, but the correctness depends on the data shape more than on the window function itself.

• If duplicate rows exist for a period, aggregate first or the baseline may be duplicated.
• If the prior period is zero, the percentage-growth formula can divide by zero and needs defensive handling.
• If your comparison depends on fiscal calendars rather than calendar years, the time dimension needs to encode that explicitly.
• If the source table is event-level and large, the expensive part is often building the yearly or monthly aggregate before LAG() ever runs.

The window function is the easy part. The hard part is making sure the ordered rows are the periods the business thinks they are.

When NOT to Use YoY Growth

Do not default to year-over-year reporting when the business context makes it misleading.

Examples:

• new products that do not have a mature prior-year baseline
• highly seasonal businesses where month-over-month or week-over-week may answer the operational question better
• very sparse datasets where one extreme period makes the growth rate look dramatic but not meaningful

YoY is strongest for stable, recurring time series. If the product line is new or the baseline is incomplete, absolute change or rolling-trend analysis may be the safer story.

Official References

• PostgreSQL window function documentation for LAG, offsets, and ordered-row behavior.
• SQLite window function documentation for SQLite support and syntax.
• PostgreSQL date/time functions and operators for the calendar-aware operations often needed when building proper comparable periods.

Conclusion

LAG() is indispensable for financial and trend analysis. It turns complex self-joins into simple, readable window functions.

Related Articles

• Mastering SQL LEAD and LAG Functions for Row Comparisons for the row-comparison foundation behind year-over-year reporting.
• Calculating Moving Averages and Rolling Windows in SQL for the smoothing layer that often sits next to growth-rate analysis.
• Time Series Analysis with SQL: A Practical Guide for the broader set of trend and period-over-period reporting patterns.