SQL Query to Calculate Rolling Average

You are looking at a table of daily sales figures and something immediately feels off. Day 14 shows revenue of 48,000 dollars. Day 15 drops to 9,000 dollars. Day 16 jumps back to 52,000 dollars. Is day 15 a real problem or just a Tuesday? Is the overall trend going up or down? Looking at the raw numbers day by day makes it almost impossible to tell.

This is exactly the situation a rolling average was built for. Instead of showing you what happened on a single day, a rolling average shows you the average of the last several days at each point in time. The spikes and drops get smoothed out and the underlying trend becomes visible. You can see whether the business is genuinely growing, declining, or staying flat, without a single outlier distorting the picture.

In SQL, calculating a rolling average is done using window functions, specifically the AVG() function combined with an OVER clause that defines a sliding window of rows. It is one of the most useful analytical patterns in SQL and once you understand how the window definition works, you can adapt it to calculate rolling sums, rolling counts, and rolling maximums using the exact same structure.

This guide walks through calculating rolling averages in SQL from scratch, with real examples, variations for different window sizes, and practical use cases.

What Is a Rolling Average in SQL?

A rolling average, also called a moving average, calculates the average of a value over a fixed number of preceding rows at each point in your dataset. Instead of a single average across the whole table, you get a separate average for every row, each one calculated using only the rows within the defined window.

Think of it like looking at your monthly spending through a sliding window. If you use a three month rolling average, January shows the average of January alone. February shows the average of January and February. March shows the average of January, February, and March. April shows the average of February, March, and April, and so on. Each month the window moves forward by one, always averaging the most recent three months and dropping the oldest one.

In SQL that sliding window is defined using the OVER clause with a ROWS BETWEEN specification that tells the database exactly how many preceding rows to include in each calculation.

Setting Up the Example Table

The examples in this guide use a daily sales table for a retail business. Create and populate it with this SQL:

sql

CREATE TABLE daily_sales (
    sale_date   DATE,
    revenue     DECIMAL(10,2)
);

INSERT INTO daily_sales VALUES
('2024-01-01', 12000),
('2024-01-02', 15500),
('2024-01-03', 9800),
('2024-01-04', 18200),
('2024-01-05', 21000),
('2024-01-06', 7500),
('2024-01-07', 14300),
('2024-01-08', 19800),
('2024-01-09', 23400),
('2024-01-10', 16700),
('2024-01-11', 11200),
('2024-01-12', 28900),
('2024-01-13', 17600),
('2024-01-14', 22100),
('2024-01-15', 9400);

This table has enough rows to show clearly how the rolling window moves forward day by day and how different window sizes produce different levels of smoothing.

Step by Step: Calculating a Rolling Average in SQL

Step 1: Understand the Basic Window Function Structure

Before writing the rolling average query, understand what each part of a window function does:

sql

AVG(revenue) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

AVG(revenue) is the function being applied across the window. OVER() tells SQL this is a window function, not a regular aggregate. ORDER BY sale_date defines the order in which rows are processed, which determines what preceding and following mean. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW defines the window size, in this case the current row plus the two rows before it, which gives a three row window.

Step 2: Write a 3-Day Rolling Average

sql

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

Result:

sale_date    revenue    rolling_avg_3day
2024-01-01   12000.00   12000.00
2024-01-02   15500.00   13750.00
2024-01-03   9800.00    12433.33
2024-01-04   18200.00   14500.00
2024-01-05   21000.00   16333.33
2024-01-06   7500.00    15566.67
2024-01-07   14300.00   14266.67
2024-01-08   19800.00   13866.67
2024-01-09   23400.00   19166.67
2024-01-10   16700.00   19966.67

On January 1 there are no preceding rows so the average is just the single day value. On January 2 there is one preceding row so the average covers two days. From January 3 onward the window is fully populated and every average covers exactly three days. This behavior where early rows use a smaller window is called an expanding window and it is the default. If you want to exclude early rows where the window is not fully populated you can use a WHERE clause or a subquery to filter them out.

Step 3: Write a 7-Day Rolling Average

The only thing that changes for a different window size is the ROWS BETWEEN specification. A 7-day rolling average uses 6 PRECEDING instead of 2 PRECEDING:

sql

SELECT
    sale_date,
    revenue,
    ROUND(
        AVG(revenue) OVER (
            ORDER BY sale_date
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ), 2
    ) AS rolling_avg_7day
FROM daily_sales
ORDER BY sale_date;

A 7-day window smooths out weekly patterns like the natural drop in sales on certain days of the week. It takes longer to react to genuine trend changes but is much less influenced by individual outliers. A 3-day window reacts faster but is noisier. The right window size depends on how much smoothing your analysis needs and how quickly you need the average to reflect real changes in the underlying trend.

Step 4: Show Both Rolling Averages Side by Side

Showing multiple rolling averages in the same query makes it easy to compare how different window sizes smooth the same data:

sql

SELECT
    sale_date,
    revenue,
    ROUND(AVG(revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) AS rolling_avg_3day,
    ROUND(AVG(revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2) AS rolling_avg_7day
FROM daily_sales
ORDER BY sale_date;

Each window function in the SELECT clause is calculated independently. You can have as many window functions as you need in a single query, each with its own OVER definition, without any performance penalty for the additional window specifications.

Real World Examples

Example 1: Rolling Average by Category Using PARTITION BY

When your data contains multiple groups, like sales figures for multiple product categories or multiple store locations, you need the rolling average to reset for each group instead of flowing across all rows combined. PARTITION BY handles this:

sql

SELECT
    sale_date,
    category,
    revenue,
    ROUND(AVG(revenue) OVER (
        PARTITION BY category
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2) AS rolling_avg_7day
FROM category_sales
ORDER BY category, sale_date;

PARTITION BY category tells the window function to treat each category as a completely separate dataset. The rolling average for Electronics resets independently from the rolling average for Clothing. Without PARTITION BY the window would slide across all rows from all categories combined, which produces meaningless results when your data has natural groupings.

Example 2: Rolling Average for Monthly Revenue Trend

For monthly data a 3-month rolling average is one of the most commonly used business metrics, equivalent to a quarterly trend line that updates every month:

sql

SELECT
    DATE_TRUNC('month', sale_date)          AS month,
    SUM(revenue)                             AS monthly_revenue,
    ROUND(AVG(SUM(revenue)) OVER (
        ORDER BY DATE_TRUNC('month', sale_date)
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2)                                    AS rolling_avg_3month
FROM daily_sales
GROUP BY DATE_TRUNC('month', sale_date)
ORDER BY month;

This query first aggregates daily data to monthly totals using GROUP BY, then applies the rolling average window function over those monthly totals. Nesting an aggregate function inside a window function like AVG(SUM(revenue)) is valid SQL and a common pattern when you need to roll up raw data before applying a window calculation.

Example 3: Excluding Incomplete Windows With a Filter

By default the rolling average uses however many rows are available for early rows in the dataset. If you want to exclude rows where the window is not fully populated, filter on the row number:

sql

SELECT
    sale_date,
    revenue,
    rolling_avg_7day
FROM (
    SELECT
        sale_date,
        revenue,
        ROUND(AVG(revenue) OVER (
            ORDER BY sale_date
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ), 2) AS rolling_avg_7day,
        ROW_NUMBER() OVER (ORDER BY sale_date) AS row_num
    FROM daily_sales
) subquery
WHERE row_num >= 7
ORDER BY sale_date;

The inner query calculates the rolling average and assigns a row number to each row. The outer query filters to only rows where at least 7 rows of data exist before and including the current row. This ensures every rolling average value in the result is based on a full 7-day window.

Example 4: Rolling Average Compared to Overall Average

Comparing the rolling average to the overall average in the same query immediately shows which periods performed above or below the long-term baseline:

sql

SELECT
    sale_date,
    revenue,
    ROUND(AVG(revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2)                                           AS rolling_avg_7day,
    ROUND(AVG(revenue) OVER (), 2)                  AS overall_avg,
    ROUND(AVG(revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) - AVG(revenue) OVER (), 2)                    AS difference_from_avg
FROM daily_sales
ORDER BY sale_date;

AVG(revenue) OVER () with an empty OVER clause calculates the average across all rows in the dataset. Subtracting the overall average from the rolling average gives you a difference column that is positive when the recent trend is above the long-term baseline and negative when it is below it.

How SQL Rolling Average Window Functions Work Internally

When SQL processes a window function, it first sorts the rows according to the ORDER BY clause inside the OVER definition. Then for each row it identifies the subset of rows defined by the ROWS BETWEEN specification and applies the aggregate function to that subset. The result is written to that row’s output column and the process moves to the next row, sliding the window forward by one position.

The database engine optimizes this so the window does not recalculate from scratch for every row. It maintains a running calculation and adjusts it as rows enter and exit the window, which makes window functions significantly more efficient than equivalent subquery approaches that recalculate the full average for every row independently.

ROWS BETWEEN Options Reference

Specification	What It Means
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW	Current row plus 6 rows before it, 7 rows total
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW	Current row plus 2 rows before it, 3 rows total
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING	Current row plus one before and one after, 3 rows total
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW	All rows from the start up to the current row
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING	Current row to the end of the dataset
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING	All rows, same as an empty OVER clause

Common Limitations

Rolling averages lag behind sudden real changes. Because the rolling average includes historical rows, a genuine sudden drop in sales takes several days to fully show up in the rolling average. The longer the window, the more the average lags behind the true current trend. This is a fundamental property of moving averages, not a bug, but it means rolling averages are better for identifying sustained trends than detecting sudden changes.

ROWS BETWEEN counts physical rows, not time gaps. If your data has missing dates, a 7-row window does not equal a 7-day window. January 1 to January 5 with January 3 missing gives you a 5-row window that actually spans 5 days but only contains 4 data points. For time series data with gaps, consider filling missing dates before applying rolling averages or use RANGE BETWEEN with interval specifications where your database supports it.

Not all databases support window functions identically. The ROWS BETWEEN syntax covered here works in PostgreSQL, MySQL 8.0 and above, SQL Server, BigQuery, Snowflake, and Redshift. Older MySQL versions below 8.0 do not support window functions at all and require correlated subquery workarounds that are significantly slower and harder to read.

Common Mistakes to Avoid

Forgetting ORDER BY inside the OVER clause. A window function without ORDER BY inside OVER has no defined row order, which means the window boundary has no meaning. Some databases will error. Others will return results that look plausible but are calculated against an arbitrary row ordering that changes between query executions. Always include ORDER BY inside OVER for rolling calculations.

Using the wrong number in ROWS BETWEEN for the window size you want. ROWS BETWEEN N PRECEDING AND CURRENT ROW includes N plus 1 rows total, the N preceding rows and the current row itself. For a 7-day rolling average you need 6 PRECEDING, not 7 PRECEDING. 7 PRECEDING gives you an 8-row window. Write it out and count to verify before using in production.

Mixing up PARTITION BY and GROUP BY. PARTITION BY inside a window function divides the dataset into groups for the window calculation without collapsing rows. GROUP BY collapses rows into one row per group. Using GROUP BY when you meant PARTITION BY removes the detail rows you need for the rolling calculation. If you want a rolling average per category while keeping every individual row visible, use PARTITION BY.

Not rounding the result. AVG() in SQL often returns a result with many decimal places. In most analytical contexts a rolling average rounded to two decimal places is more readable and just as useful as the full precision result. Always wrap the window function in ROUND() before presenting results to business users.

Rolling Average SQL Cheat Sheet

Task	SQL Pattern
3-day rolling average	AVG(col) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
7-day rolling average	AVG(col) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
Rolling average per group	AVG(col) OVER (PARTITION BY group ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
Rolling average on aggregated data	AVG(SUM(col)) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
Overall average for comparison	AVG(col) OVER ()
Exclude incomplete windows	WHERE ROW_NUMBER() OVER (ORDER BY date) >= window_size
Centered rolling average	ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
Round to 2 decimal places	ROUND(AVG(col) OVER (…), 2)

Rolling averages turn noisy day-by-day data into readable trend lines. The SQL to calculate them is short, the logic is consistent across window sizes, and the same pattern scales from a 3-day window on daily sales to a 12-month window on annual revenue figures.

The core of the pattern never changes. Use AVG() with an OVER clause. Include ORDER BY inside OVER to define row ordering. Use ROWS BETWEEN N PRECEDING AND CURRENT ROW where N is your window size minus one. Add PARTITION BY when your data has groups that should be calculated independently. Wrap everything in ROUND() before presenting results.

Start with a 7-day rolling average on whatever time series data you work with most. Compare it to the raw values in the same query and look at how much smoother the trend line becomes. Then try a 30-day window on the same data and see how much more it smooths, and how much longer it takes to react to a real change. That intuition for how window size affects the result is what separates analysts who use rolling averages effectively from those who just pick an arbitrary window and hope it looks reasonable.

FAQs