DEV Community

Cover image for SQL Pattern Series #21: The Rolling Average Pattern
Baldwin Apps
Baldwin Apps

Posted on

SQL Pattern Series #21: The Rolling Average Pattern

#sql #database #beginners #tutorial

Some datasets are noisy.

Daily sales fluctuate.
Website traffic spikes.
Sensor readings jump around.

When you look at the raw values, it can be difficult to see what is actually happening.

The Rolling Average Pattern helps smooth out short-term fluctuations so the underlying trend becomes easier to see.

The Pattern

A rolling average uses a window function with a moving frame.

AVG(column) OVER (
    ORDER BY date
    ROWS BETWEEN n PRECEDING
    AND CURRENT ROW
)
Enter fullscreen mode Exit fullscreen mode

The frame determines how many previous rows are included in the calculation.

For example:

ROWS BETWEEN 6 PRECEDING
AND CURRENT ROW
Enter fullscreen mode Exit fullscreen mode

creates a 7-row rolling average.

Example

Suppose we want a rolling 7-day average of sales.

SELECT
    date,
    value,

    AVG(value) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING
        AND CURRENT ROW
    ) AS RollingAvg

FROM SalesData;
Enter fullscreen mode Exit fullscreen mode

Instead of showing only today's value, the query calculates the average across the current row and the previous six rows.

This smooths out day-to-day fluctuations.

Intermediate Note

The frame definition is important.

ROWS BETWEEN 6 PRECEDING
AND CURRENT ROW
Enter fullscreen mode Exit fullscreen mode

means:

  • Current row
  • Previous 6 rows

for a total of 7 rows.

Changing the frame changes the smoothing effect.

Examples:

ROWS BETWEEN 2 PRECEDING
AND CURRENT ROW
Enter fullscreen mode Exit fullscreen mode

3-row rolling average

ROWS BETWEEN 29 PRECEDING
AND CURRENT ROW
Enter fullscreen mode Exit fullscreen mode

30-row rolling average

Larger windows create smoother trends but react more slowly to changes.

Why This Pattern Matters

Raw data often contains noise.

Rolling averages help reveal the signal hidden underneath.

Common uses include:

  • Sales trends
  • Revenue analysis
  • Website traffic monitoring
  • Sensor measurements
  • Financial data
  • Operational metrics
  • Capacity planning

The goal is not to replace the raw data.

The goal is to make trends easier to see.

Thinking in Windows

A useful mental model is to imagine a small window moving down the result set.

At each row:

  1. Collect the rows inside the window.
  2. Calculate the average.
  3. Move the window forward one row.
  4. Repeat.

The average changes gradually because most of the rows in the window remain the same from one calculation to the next.

SQL Dialect Note

Rolling averages are supported by modern versions of:

  • SQL Server
  • PostgreSQL
  • MySQL 8+
  • MariaDB
  • Oracle

Window functions are one of the most powerful features in modern SQL and are widely used in reporting and analytics workloads.

When I Reach for This Pattern

I reach for this pattern whenever someone says:

"The numbers are all over the place."

A rolling average often reveals the trend that the raw data is hiding.

Key Takeaway

The Rolling Average Pattern smooths short-term fluctuations so long-term trends become easier to understand.

Sometimes the most important story in the data isn't today's value.

It's the direction things have been moving over time.

SQL Bubble Pop

I created SQL Bubble Pop, a mobile game that teaches SQL concepts through quick, interactive challenges and pattern recognition exercises.

The goal is simple:

Learn SQL by recognizing patterns instead of memorizing syntax.

Top comments (0)