SQL Growth Analysis - Calculating MoM and YoY Changes

Introduction

As a data analyst, one of the most common tasks is measuring business growth over time. Month-over-Month (MoM) and Year-over-Year (YoY) calculations are essential metrics that help businesses understand their performance trends. Think of MoM as comparing this month’s performance to last month, while YoY compares it to the same month last year.

Why Calculate Growth Rates?

Every business needs to track its growth. Common questions include:

• “How did our sales grow compared to last month?”
• “Are we performing better than the same season last year?”
• “What’s our average growth rate over the past 3 months?”

Let’s learn how to answer these questions using SQL.

Main Content: Understanding Growth Calculations

Let’s use this simple sales data for our examples:

-- Create our monthly sales table
CREATE TABLE monthly_sales (
    sale_date DATE,
    product_name VARCHAR(100),
    revenue DECIMAL(10,2)
);

-- Insert sample data
INSERT INTO monthly_sales VALUES
('2023-01-15', 'Product A', 1000),
('2023-02-15', 'Product A', 1200),
('2023-03-15', 'Product A', 1100),
('2024-01-15', 'Product A', 1300),
('2024-02-15', 'Product A', 1500),
('2024-03-15', 'Product A', 1400);

Example 1: Month-over-Month Growth

Let’s calculate how sales grew each month:

SELECT 
    sale_date,
    revenue as current_month,
    LAG(revenue) OVER (ORDER BY sale_date) as previous_month,
    ROUND(
        ((revenue - LAG(revenue) OVER (ORDER BY sale_date)) 
        / LAG(revenue) OVER (ORDER BY sale_date) * 100),
    2) as growth_percent
FROM monthly_sales
ORDER BY sale_date;

Let’s break down this query:

1. LAG(revenue):

   • Looks back one row to get previous month’s revenue
   • Works like looking at last month’s sales report

2. Growth Formula:

   • (current - previous) / previous * 100
   • Shows percentage increase/decrease
   • Positive = growth, Negative = decline

Results:

sale_date  | current_month | previous_month | growth_percent
-----------|---------------|----------------|---------------
2023-01-15 | 1000.00      | NULL           | NULL
2023-02-15 | 1200.00      | 1000.00        | 20.00
2023-03-15 | 1100.00      | 1200.00        | -8.33
2024-01-15 | 1300.00      | 1100.00        | 18.18
2024-02-15 | 1500.00      | 1300.00        | 15.38
2024-03-15 | 1400.00      | 1500.00        | -6.67

Example 2: Year-over-Year Growth

Now, let’s compare sales with the same month last year:

SELECT 
    s1.sale_date,
    s1.revenue as current_year,
    s2.revenue as last_year,
    ROUND(
        ((s1.revenue - s2.revenue) / s2.revenue * 100),
    2) as yoy_growth
FROM monthly_sales s1
LEFT JOIN monthly_sales s2 
    ON DATE_PART('month', s1.sale_date) = DATE_PART('month', s2.sale_date)
    AND DATE_PART('year', s1.sale_date) = DATE_PART('year', s2.sale_date) - 1
WHERE s2.revenue IS NOT NULL
ORDER BY s1.sale_date;

Understanding the query:

1. DATE_PART('month', sale_date):

   • Extracts month from date
   • Matches same months across years

2. DATE_PART('year', sale_date) - 1:

   • Finds previous year’s data
   • Enables year-to-year comparison

Results:

sale_date  | current_year | last_year | yoy_growth
-----------|--------------|-----------|------------
2024-01-15 | 1300.00     | 1000.00   | 30.00
2024-02-15 | 1500.00     | 1200.00   | 25.00
2024-03-15 | 1400.00     | 1100.00   | 27.27

Example 3: Rolling Average Growth

Calculate 3-month average to smooth out fluctuations:

SELECT 
    sale_date,
    revenue,
    ROUND(
        AVG(revenue) OVER (
            ORDER BY sale_date 
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ),
    2) as rolling_avg
FROM monthly_sales
ORDER BY sale_date;

Query explanation:

1. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW:
   • Creates 3-month window
   • Includes current and previous 2 months
   • Smooths out monthly variations

Results:

sale_date  | revenue | rolling_avg
-----------|---------|-------------
2023-01-15 | 1000.00 | 1000.00
2023-02-15 | 1200.00 | 1100.00
2023-03-15 | 1100.00 | 1100.00
2024-01-15 | 1300.00 | 1200.00
2024-02-15 | 1500.00 | 1300.00
2024-03-15 | 1400.00 | 1400.00

Best Practices

1. Data Quality

   • Ensure dates are properly formatted
   • Handle missing months appropriately
   • Validate your growth calculations

2. Performance Tips

   • Index your date columns
   • Use appropriate data types
   • Consider materialized views for large datasets

3. Common Mistakes to Avoid

   • Not handling division by zero
   • Forgetting about seasonal patterns
   • Missing data gaps in time series

Remember: Growth calculations help track progress, but always consider business context when interpreting results!

Happy analyzing! 📈