Learn Year-over-Year Analysis | Period-over-Period Comparisons

Year-over-year (YoY) analysis is a cornerstone of financial reporting, allowing you to compare performance for the same period across different years. This approach helps you spot long-term trends, seasonality, and growth patterns that might not be visible when looking at shorter time frames. In finance and accounting, YoY comparisons are especially useful for evaluating revenue, expenses, or other key metrics, as they remove the effects of short-term fluctuations and provide a clear view of overall business direction.


              12345678910
            
-- Group revenue by year and month to prepare for YoY analysis
SELECT
    EXTRACT(YEAR FROM revenue_date) AS year,
    EXTRACT(MONTH FROM revenue_date) AS month,
    SUM(amount) AS total_revenue
FROM revenue
GROUP BY
    EXTRACT(YEAR FROM revenue_date),
    EXTRACT(MONTH FROM revenue_date)
ORDER BY year, month;

To perform a meaningful year-over-year (YoY) analysis, you need to structure your data so that each period—such as each month—can be compared directly across years. This usually means aggregating your data by both year and month, as shown above, so that you can align January 2023 with January 2022, and so on. Once the data is grouped, you can use window functions to look back at the same period in the previous year and calculate differences or growth rates.


              1234567891011121314
            
-- Compare each month's revenue to the same month in the previous year using LAG() and PARTITION BY month
SELECT
    EXTRACT(YEAR FROM revenue_date) AS year,
    EXTRACT(MONTH FROM revenue_date) AS month,
    SUM(amount) AS total_revenue,
    LAG(SUM(amount)) OVER (
        PARTITION BY EXTRACT(MONTH FROM revenue_date)
        ORDER BY EXTRACT(YEAR FROM revenue_date)
    ) AS last_year_revenue
FROM revenue
GROUP BY
    EXTRACT(YEAR FROM revenue_date),
    EXTRACT(MONTH FROM revenue_date)
ORDER BY month, year;

The key to comparing the same month across different years is the use of window functions with partitioning. By using the PARTITION BY clause inside a window function, you tell SQL to treat each month as its own group, so the window function—such as LAG()—operates within each month across all years. This way, you can easily access the value from the same month in the previous year, making year-over-year calculations straightforward and reliable.

Note

To calculate the YoY percentage change, you can extend the previous query by adding a calculation such as (total_revenue - last_year_revenue) / last_year_revenue * 100. This gives you the percentage growth or decline for each period.

Everything was clear?

Thanks for your feedback!

Section 2. Chapter 3

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Suggested prompts:

Can you explain how the LAG() function works in this context?

How do I calculate the YoY growth percentage using this query?

What should I do if my data has missing months or years?

Swipe to show menu


              12345678910
            
-- Group revenue by year and month to prepare for YoY analysis
SELECT
    EXTRACT(YEAR FROM revenue_date) AS year,
    EXTRACT(MONTH FROM revenue_date) AS month,
    SUM(amount) AS total_revenue
FROM revenue
GROUP BY
    EXTRACT(YEAR FROM revenue_date),
    EXTRACT(MONTH FROM revenue_date)
ORDER BY year, month;


              1234567891011121314
            
-- Compare each month's revenue to the same month in the previous year using LAG() and PARTITION BY month
SELECT
    EXTRACT(YEAR FROM revenue_date) AS year,
    EXTRACT(MONTH FROM revenue_date) AS month,
    SUM(amount) AS total_revenue,
    LAG(SUM(amount)) OVER (
        PARTITION BY EXTRACT(MONTH FROM revenue_date)
        ORDER BY EXTRACT(YEAR FROM revenue_date)
    ) AS last_year_revenue
FROM revenue
GROUP BY
    EXTRACT(YEAR FROM revenue_date),
    EXTRACT(MONTH FROM revenue_date)
ORDER BY month, year;

Note

Everything was clear?

Thanks for your feedback!

Section 2. Chapter 3

Year-over-Year Analysis

1. What does PARTITION BY do in a window function?

2. How can you compare the same month across different years in SQL?

3. What is the benefit of year-over-year analysis?

Year-over-Year Analysis

1. What does PARTITION BY do in a window function?

2. How can you compare the same month across different years in SQL?

3. What is the benefit of year-over-year analysis?