In this lesson, you will learn how to smooth out volatile time-series data using window functions to reveal underlying trends.
Why Smooth Data?
When you analyze daily data—like website traffic, sales, or stock prices—the numbers often bounce up and down due to natural "noise" (e.g., weekends vs. weekdays). If you plot this raw data, the chart looks jagged, making it hard to see the overall direction. A Moving Average fixes this by taking the average of a specific number of preceding rows, effectively creating a "smoother" line that highlights long-term trends rather than daily fluctuations.
Calculating the Trend
To calculate a moving average in SQL, we use the AVG() aggregate function paired with a OVER clause. By defining a window that looks at the current row and a set number of previous rows, we can calculate the average for that specific slice of time.
Consider a table daily_sales with columns sale_date and amount. To calculate a 7-day moving average, we write:
SELECT
sale_date,
amount,
AVG(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_avg_7_day
FROM daily_sales;
Breaking Down the Window
ORDER BY sale_date: This ensures the database processes the records chronologically.ROWS BETWEEN 6 PRECEDING AND CURRENT ROW: This defines our "frame." Since we include the current day, "6 preceding" plus the "current row" gives us a total window of 7 days.
As the query moves to the next date, the window "slides" forward, dropping the oldest day and adding the newest one. This creates a continuous stream of averages. If your data has gaps (which we learned to handle in previous lessons), you can combine this with COALESCE or filling logic to ensure your moving average doesn't break when a day is missing. By applying this, you transform jittery data into a clear, actionable trend line.
Key Takeaway: Moving averages turn noisy, volatile time-series data into clear insights by calculating the mean across a sliding window of recent records.