Calculating Moving Averages and Rolling Windows in SQL

Data is noisy. Daily sales spike on weekends, drop on holidays, and fluctuate randomly. To see the real trend, analysts use Moving Averages (or Rolling Averages).

A moving average takes the current value and averages it with a set number of previous values.

A Real Business Scenario: Distinguishing Trend from Noise

Teams usually reach for moving averages when raw daily values are too noisy to act on directly.

Common examples:

• finance wants a smoother revenue trend than one-day spikes
• operations wants to see baseline traffic without every campaign burst dominating the chart
• product teams want to separate short-lived anomalies from durable growth

The moving average is valuable because it preserves sequence while reducing volatility. It does not replace the raw metric. It gives you a second lens for interpreting it.

The Window Function Formula

To calculate a moving average in SQL, you use the standard AVG() aggregate function but apply it over a "window" of rows.

AVG(column) OVER (
  ORDER BY date_column
  ROWS BETWEEN X PRECEDING AND CURRENT ROW
)

• ORDER BY date_column: Ensures rows are processed in chronological order.
• ROWS BETWEEN: Defines the "frame size".
  • For a 7-Day Moving Average, you want today + the previous 6 days. So: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW.
  • For a 3-Day Moving Average: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.

A Common Mistake: Confusing Row Windows with Calendar Windows

This is one of the most common moving-average mistakes:

AVG(value) OVER (
  ORDER BY event_date
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)

That is a 7-row moving average, not necessarily a 7-day moving average. If dates are missing, weekends are excluded, or data is sparse, the frame still counts rows, not calendar days.

If the business question is truly time-based, you may need a filled date spine or a database feature that supports interval-aware range framing.

Real-World Example: Daily Stock Prices

Let's look at a volatile stock price and smooth it out.

1. Daily Volatility

First, observe the raw daily prices.

2. Calculating the 3-Day Moving Average

Now, let's calculate the 3-day moving average. For Jan 3rd, it will average prices from Jan 1, 2, and 3.

Result Analysis:

• On Jan 4, the price dropped to $90.
• The Moving Average was (102+104+90)/3 = 98.66.
• Notice how the moving average ($98.66) is less extreme than the drop ($90), showing the smoothing effect.

Handling the "Start" of the Window

You might notice that the first row is just the average of itself, and the second row is the average of 2 days. The window grows until it hits the full size (3 days).

If you want to strictly enforce that you ONLY calculate when you have full 3 days of data, you'd need slightly more complex logic (often involving COUNT() OVER (...) to check the window size), but for most visual trend/charting purposes, the growing window at the start is acceptable.

Centered vs Trailing Windows

The examples above differ from "Centered" moving averages, which look ahead and behind.

• Trailing (Standard): ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
• Centered: ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

Centered averages are good for historical analysis but impossible for real-time forecasting (since you don't know tomorrow's price yet!).

Boundary and Performance Notes

Moving averages are usually straightforward after the underlying grain is correct, but there are still common pitfalls:

• duplicate timestamps can make ordering ambiguous unless you add a deterministic tie-breaker
• sparse time series can make a row-based frame tell the wrong business story
• large partitions require sorting, so indexes or clustering on the ordering column still matter
• smoothing can hide the very spikes that an operations or fraud team needs to investigate

The right workflow is often:

1. establish the correct daily or hourly aggregate
2. confirm the ordering column really defines the series
3. decide whether the window is row-based or calendar-based
4. plot both the raw metric and the smoothed metric

When NOT to Use a Moving Average

Avoid defaulting to a moving average when:

• the audience needs exact daily values for operational action
• the anomaly itself is more important than the baseline trend
• the series is too short for the chosen window size to be meaningful

A moving average is a smoothing tool, not a universal "better metric".

Official References

• PostgreSQL window function documentation for window-function semantics and framing concepts.
• SQLite window function documentation for SQLite support and syntax.
• PostgreSQL tutorial on window functions for a readable explanation of ordered analytical calculations.

Practice Challenge

Try modifying the query to calculate a Cumulative Average (running average of all time) by removing the ROWS clause entirely or using ROWS UNBOUNDED PRECEDING.

Conclusion

Window functions with frame specifications (ROWS BETWEEN) give you precise control over rolling calculations. Whether it's stock prices, website traffic, or server load, Moving Averages help you ignore the noise and focus on the signal.

Related Articles

• Calculating Running Totals & Moving Averages in SQL for the adjacent cumulative-metric pattern that often appears alongside smoothing.
• SQL Window Frames: ROWS vs RANGE for the frame semantics that cause many subtle rolling-metric bugs.
• Time Series Analysis with SQL: A Practical Guide for the broader analytical context where moving averages become useful.