Comparing Period-over-Period Performance

In this lesson, you will learn how to align disparate time periods within a single result set to calculate meaningful growth trends.

When analyzing performance—such as Month-over-Month (MoM) or Year-over-Year (YoY) growth—the primary challenge is that your current data and your historical comparison data reside in different rows. To visualize these together, you must bridge that gap. While window functions like LAG() are excellent for simple sequential lookups, joining tables (or CTEs) against themselves or a calendar dimension allows for more robust, scalable comparisons.

Aligning Periods via Self-Joins

The most flexible approach for period-over-period analysis is to create two distinct sets of aggregated data—the "Current" and the "Prior"—and join them based on a relative time offset.

For example, if you want to compare current monthly sales to the same month last year, you can use a CTE to generate your aggregates and join them:

WITH MonthlySales AS (
    SELECT 
        DATE_TRUNC('month', order_date) AS report_month,
        SUM(amount) AS total_sales
    FROM sales
    GROUP BY 1
)
SELECT 
    curr.report_month,
    curr.total_sales AS current_period,
    prev.total_sales AS prior_period,
    (curr.total_sales - prev.total_sales) / NULLIF(prev.total_sales, 0) AS growth_rate
FROM MonthlySales curr
LEFT JOIN MonthlySales prev 
    ON curr.report_month = prev.report_month + INTERVAL '1 year';

Why This Matters

By treating the current and prior periods as distinct entities, you gain several advantages:

1. Handling Gaps: If a specific month had no sales, a standard window function might calculate growth based on the previous available record. A join against a calendar dimension or the dataset itself ensures you are comparing the correct logical timeframes (e.g., this October vs. last October).
2. Multiple Comparisons: You can easily extend this pattern to compare against multiple offsets simultaneously (e.g., comparing current performance against both the previous month and the same month from the previous year) by adding additional join conditions.
3. Efficiency: For large datasets, ensuring your join keys are indexed or part of your cluster keys (as discussed in Lesson 6) prevents performance degradation during the comparison phase.

Key Takeaway: To accurately compare performance across time, shift the "prior" time dimension relative to your "current" dimension using joins or window offsets to align historical values with their current counterparts.