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.

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)

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".

Conclusion

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