How to Calculate Moving Averages and Analyze Time Series in SQL

A time series is a set of values recorded in time order, like daily sales or hourly website visits. These numbers jump around from day to day, and the noise can hide the real direction. A moving average smooths this by averaging each value with the previous ones.

Building a 3-Day Moving Average

Take a daily_sales table with the units sold over a week.

SELECT sale_date,
       units_sold,
       ROUND(AVG(units_sold) OVER (
           ORDER BY sale_date
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 1) AS moving_avg_3day
FROM daily_sales;

Output:

Code Explanation:

• ORDER BY sale_date puts the rows in time order, which the window needs.
• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW defines the window as the current day plus the two days before it.
• AVG is calculated over that window and slides forward one row at a time.
• The first two days have fewer than three rows, so they average whatever is available.

The graph shows the effect. The daily line swings between 31 and 63, while the moving average stays much calmer and reveals the gentle upward trend.

Figure 2: SQL window function example comparing daily sales with a 3-day moving average

Looking at the Data by Month or Week

For longer series, summarize before you smooth. DATE_TRUNC('month', sale_date) rounds each date down to the first day of its month, so you can group by it and get monthly totals. Combine this with LAG from the last lesson to compare each month with the previous one.

Things to Watch For

• Missing dates: ROWS counts rows, not days. If a day is absent from the table, the window quietly stretches over a longer period, so fill the gaps first if accuracy matters.
• Window size: a short window follows the data closely, while a long one smooths more but reacts more slowly. Pick the size based on what you want to see.