Calculating Year-Over-Year Growth

Compare current performance against historical milestones using windowed joins.

Comparing Growth with Windowed Joins

In this lesson, you will learn how to calculate Year-Over-Year (YoY) growth by aligning current performance metrics with historical milestones using window functions.

The Core Concept

To calculate YoY growth, you need to compare a value from the current year to the exact same metric from the previous year. While we have previously looked at calculating running deltas or identifying gaps, YoY growth requires a self-join or a window function approach to "look back" exactly 365 days or one year into the past.

Instead of creating complex joins, we can use the LAG() window function. This function allows us to peer into a previous row within our result set without needing to join the table back to itself.

Practical Implementation

Imagine you have a table yearly_sales with columns sales_year and total_revenue. To compute the growth rate, you first retrieve the total_revenue from the prior row and then apply the growth formula: ((Current - Prior) / Prior) * 100.

SELECT 
    sales_year,
    total_revenue,
    LAG(total_revenue) OVER (ORDER BY sales_year) AS prev_year_revenue,
    ((total_revenue - LAG(total_revenue) OVER (ORDER BY sales_year)) 
      / LAG(total_revenue) OVER (ORDER BY sales_year)) * 100 AS pct_growth
FROM yearly_sales;

Why This Works

The LAG() function acts as a pointer to the previous record based on the ORDER BY clause. By partitioning the window (if you had multiple regions or product categories, you would use PARTITION BY), you ensure the look-back logic remains scoped to the correct entity.

Using windowed functions for this task is significantly more efficient than a self-join because the database engine processes the data in a single pass rather than building a temporary join table. This technique transforms raw historical data into actionable business intelligence, allowing you to see if your current trajectory is improving or declining compared to your past milestones.

Key Takeaway: Use the LAG() window function to offset your data by one period, enabling seamless YoY growth calculations without the complexity of self-joins.