Calculating Year-over-Year (YoY) Growth in SQL | Interview Preparation

Imagine you’re analyzing business performance and someone asks, “How did our sales grow compared to last year?” This seemingly simple question is actually a powerful starting point for data storytelling.

Year-over-Year (YoY) growth helps you:

• Identify growth patterns and trends
• Spot seasonal behaviors
• Compare performance across years
• Support strategic decision-making

💡 Data Tip: YoY is more stable than month-over-month (MoM) since it accounts for seasonality.

To calculate YoY growth in SQL, you must think beyond formulas. You need to:

• Understand the dataset
• Identify key metrics and granularity
• Choose an efficient method (subquery vs. window function)
• Handle edge cases like missing years or null values

Let’s walk through this problem like a data analyst would.

Problem Statement

You’re given a table with two columns:

• year (e.g., 2020, 2021, ...)
• total_sales (e.g., 50000, 60000, ...)

Your goal is to produce an output table that includes:

• Year
• Total Sales
• Previous Year Sales
• YoY Growth Percentage

Solution

To compute YoY Growth Percentage:

This formula calculates the percentage change between the current year’s value and the previous year’s value.

There are two standard SQL approaches to solve this problem:

For a step-by-step video walkthrough: check out this video-

[embed]

Approach 1: Using Subqueries

Let’s say we already have a table called sales that contains aggregated yearly sales:

SELECT 
    s.year, 
    s.total_sales, 
    (SELECT total_sales 
     FROM sales 
     WHERE year = s.year - 1) AS prev_year_sales,
    CASE 
        WHEN (SELECT total_sales FROM sales WHERE year = s.year - 1) IS NOT NULL 
        THEN ROUND(((s.total_sales - 
            (SELECT total_sales FROM sales WHERE year = s.year - 1)) 
            / (SELECT total_sales FROM sales WHERE year = s.year - 1)) * 100, 2)
        ELSE NULL 
    END AS yoy_growth
FROM sales s
ORDER BY s.year;

This query —

Retrieve year and total_sales: Basic column selection from the sales table.

Subquery for previous year:

(SELECT total_sales FROM sales WHERE year = s.year - 1)

This fetches the previous year’s sales. If no record exists (e.g., for the first year), it returns NULL.

Calculate YoY Growth: The formula is applied directly, but uses the subquery three times.

Ordering: Ensures results are sorted chronologically.

⚠️Drawbacks

• Performance: Subquery executes 3x per row, slowing down large datasets.
• Redundancy: Repeating the same subquery introduces inefficiency.

💡 SQL Tip: Avoid repeated subqueries — assign them in CTEs or use window functions where possible.

Approach 2: Using LAG() Window Function

For modern databases (PostgreSQL, MySQL 8+, SQL Server, Oracle), window functions like LAG() offer a cleaner and faster solution.

SELECT 
    s.year, 
    s.total_sales, 
    LAG(total_sales) OVER (ORDER BY year) AS prev_year_sales,
    CASE 
        WHEN LAG(total_sales) OVER (ORDER BY year) IS NOT NULL 
        THEN ROUND((total_sales - LAG(total_sales) OVER (ORDER BY year)) * 100.0 
        / LAG(total_sales) OVER (ORDER BY year), 2)
        ELSE NULL 
    END AS yoy_growth
FROM sales s
ORDER BY s.year;

• LAG(): A window function that retrieves a value from the previous row based on the ordering:

LAG(total_sales) OVER (ORDER BY year)

• Null Check: Prevents division by NULL by using a CASE statement.
• One-pass Calculation: YoY is calculated without repeating logic.

Whether you’re answering stakeholder questions, preparing for SQL interviews, or building dashboards, being able to calculate and interpret YoY trends helps you tell powerful data stories.

If you’re using older databases, subqueries will get the job done. But for modern, scalable, and cleaner SQL, window functions like LAG() are your best bet.

If you found this helpful, there’s so much more coming your way — from SQL tricks and data storytelling to real-world analytics breakdowns.📌

**Follow me for more practical SQL tips, data interview prep, and real-world analytics insights.** Let’s turn data into stories that matter. 🚀