In this lesson, you will learn how to isolate true business growth from predictable, recurring seasonal fluctuations.
The Problem: The "Noise" of Seasonality
Imagine you are analyzing sales data for a retail store. Every December, your revenue spikes due to the holidays, and every February, it dips. If you look at the raw monthly totals, your growth trend looks like a jagged mountain range. This "seasonal noise" makes it difficult to see if the business is actually improving over the long term.
The Solution: Centered Moving Averages
To smooth out these periodic bumps, we use a Moving Average. By averaging a data point with its immediate neighbors (for example, the average of the last 12 months), the seasonal "spikes" are flattened out, leaving behind a cleaner line that represents the underlying growth trend.
In SQL, we can calculate this using a WINDOW function. If you have monthly sales, a 12-month moving average looks like this:
SELECT
sale_month,
sales_amount,
AVG(sales_amount) OVER (
ORDER BY sale_month
ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
) AS smoothed_trend
FROM monthly_sales;
Why This Works
By aggregating across a full seasonal cycle (like 12 months for annual seasonality), the high months and low months effectively "cancel out" each other's extremes.
- The Raw Data: Shows you exactly what happened during a specific month.
- The Smoothed Data: Tells you the direction the business is heading, independent of the calendar.
When you use this technique, you move from reacting to temporary seasonal shifts to understanding the actual health of your data. You are filtering out the "seasonal heartbeat" to see the "long-term pulse."
Note that this process is distinct from Outlier Trimming, which removes data points that are mistakes or anomalies. Smoothing does not delete data; it reformats it to reveal the signal hidden within the cycle.
Key Takeaway: Use a windowed moving average to cancel out recurring seasonal peaks and valleys, exposing the underlying growth trend of your data.