SQL Moving Averages with Window Functions

A business analyst is reviewing daily order values and the numbers jump around too much to spot a trend. They ask you for a 3-day moving average of revenue so they can see whether sales are trending up or down. A plain AVG with GROUP BY won't work here. You need a moving average in SQL, and that means AVG() OVER with a frame clause.

How Moving Averages Work in SQL

A moving average takes a fixed number of rows, calculates their average, then slides that window forward one row at a time. For each row, you average it together with a defined number of rows before it.

The syntax that makes this work is ROWS BETWEEN:

SELECT
    column_name,
    AVG(column_name) OVER (
        ORDER BY sort_column
        ROWS BETWEEN N PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM table_name;

ROWS BETWEEN N PRECEDING AND CURRENT ROW tells SQL: for each row, include this row and the N rows before it. With N = 2, you get a 3-row window: the current row plus 2 preceding rows.

Here it is on the orders table, calculating a 3-row moving average of total_amount ordered by order_date:

SELECT
    o.order_id,
    o.order_date,
    o.total_amount,
    AVG(o.total_amount) OVER (
        ORDER BY o.order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3
FROM orders o;

The first row averages only itself. The second row averages itself and one preceding row. From the third row onward, every row averages itself with the two rows before it. This is normal behaviour when there aren't enough preceding rows yet.

Cumulative Average vs Moving Average

Before going further, it's worth knowing the difference between these two, because they use the same function but produce different results.

A cumulative average grows from the first row to the current row. It uses ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is also the default frame when you write ORDER BY inside OVER without specifying ROWS BETWEEN.

SELECT
    o.order_id,
    o.order_date,
    o.total_amount,
    AVG(o.total_amount) OVER (
        ORDER BY o.order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_avg
FROM orders o;

The cumulative average includes every row from the start. A moving average only ever looks at a fixed number of rows. Use cumulative when you want the running mean across all history. Use moving when you want to smooth recent fluctuations.

Using PARTITION BY to Reset the Window

Add PARTITION BY before ORDER BY inside OVER and the moving average calculates independently within each group. When SQL moves to a new partition, the window resets.

This query calculates a 3-row moving average of amount within each payment method:

SELECT
    pay.payment_id,
    pay.method,
    pay.payment_date,
    pay.amount,
    AVG(pay.amount) OVER (
        PARTITION BY pay.method
        ORDER BY pay.payment_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_by_method
FROM payments pay;

Each method gets its own independent moving average. The first two rows within each group still average fewer than three values, for the same reason as before.

How Moving Averages Connect to Other Concepts

Moving averages use the same OVER clause as [running totals](/learn/sql-running-totals-in-window). The difference is the frame. Running totals use UNBOUNDED PRECEDING. Moving averages use a fixed N PRECEDING. If you haven't covered ROWS BETWEEN in depth, the [ROWS vs RANGE lesson](/learn/sql-rows-vs-range-window) explains exactly how frame boundaries work and when the distinction matters.

Common Mistakes

1. Forgetting ROWS BETWEEN and getting a cumulative average instead

Without ROWS BETWEEN, SQL uses the default frame, which is UNBOUNDED PRECEDING AND CURRENT ROW. That gives a cumulative average, not a moving one.

-- wrong: cumulative average, not a moving average
SELECT
    o.order_id,
    o.order_date,
    o.total_amount,
    AVG(o.total_amount) OVER (ORDER BY o.order_date) AS moving_avg
FROM orders o;

Add ROWS BETWEEN N PRECEDING AND CURRENT ROW to fix the window size.

-- correct: 3-row moving average
SELECT
    o.order_id,
    o.order_date,
    o.total_amount,
    AVG(o.total_amount) OVER (
        ORDER BY o.order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3
FROM orders o;

2. Confusing N PRECEDING with the total window size

2 PRECEDING means two rows before the current row, giving a total window of 3 rows including the current one. It's easy to write 3 PRECEDING when you want a 3-row window, which actually gives you a 4-row window.

-- wrong: 4-row window when a 3-row window was intended
SELECT
    o.order_id,
    o.total_amount,
    AVG(o.total_amount) OVER (
        ORDER BY o.order_date
        ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM orders o;

Use N - 1 PRECEDING to get an N-row window. For a 3-row window, use 2 PRECEDING.

-- correct: 3-row window
SELECT
    o.order_id,
    o.total_amount,
    AVG(o.total_amount) OVER (
        ORDER BY o.order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3
FROM orders o;

3. Filtering on the moving average alias in WHERE

Window functions run after WHERE, so the alias isn't available at that stage. Referencing it in WHERE causes an error.

-- wrong
SELECT
    pay.payment_id,
    pay.amount,
    AVG(pay.amount) OVER (
        ORDER BY pay.payment_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM payments pay
WHERE moving_avg > 200;

Wrap the query in a subquery so the computed column exists when the filter runs.

-- correct
SELECT * FROM (
    SELECT
        pay.payment_id,
        pay.amount,
        AVG(pay.amount) OVER (
            ORDER BY pay.payment_date
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) AS moving_avg
    FROM payments pay
) t
WHERE moving_avg > 200;

Quick Recap

• A moving average in SQL uses AVG() OVER (ORDER BY ... ROWS BETWEEN N PRECEDING AND CURRENT ROW) to average each row with a fixed number of rows before it.
• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW gives a 3-row window: the current row plus 2 before it.
• The first rows in a result may average fewer values than intended because there aren't enough preceding rows yet.
• Omitting ROWS BETWEEN gives a cumulative average, not a moving one, because SQL uses UNBOUNDED PRECEDING by default.
• PARTITION BY resets the moving average for each group, so each partition calculates independently.
• To filter on the result, wrap the query in a subquery or [CTE](/learn/sql-cte-common-table-expressions) and apply WHERE in the outer query.

SELECT
    e.employee_id,
    e.hire_date,
    e.salary,
    AVG(e.salary) OVER (
        ORDER BY e.hire_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_salary
FROM employees e;

SELECT
    e.employee_id,
    e.department,
    e.hire_date,
    e.salary,
    AVG(e.salary) OVER (
        PARTITION BY e.department
        ORDER BY e.hire_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS dept_moving_avg
FROM employees e;

SELECT
    pay.payment_id,
    pay.method,
    pay.payment_date,
    pay.amount,
    AVG(pay.amount) OVER (
        ORDER BY pay.payment_date
        ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ) AS moving_avg_5
FROM payments pay;

SELECT
    pay.payment_id,
    pay.method,
    pay.payment_date,
    pay.amount,
    AVG(pay.amount) OVER (
        PARTITION BY pay.method
        ORDER BY pay.payment_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS method_moving_avg
FROM payments pay
WHERE pay.status = 'completed';

SELECT * FROM (
    SELECT
        pay.payment_id,
        pay.payment_date,
        pay.amount,
        AVG(pay.amount) OVER (
            ORDER BY pay.payment_date
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) AS moving_avg_3
    FROM payments pay
) t
WHERE moving_avg_3 > 150;