3-Day Trailing Moving Average — SQL

Solving tips

A trailing moving average is AVG(...) OVER (ORDER BY time ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) — an N-day window uses N-1 PRECEDING.
The frame counts rows physically with ROWS, so early rows average fewer values (a partial window) rather than NULL.
Use ROWS (row count) not RANGE (value range) when your window is a fixed number of observations.

You have daily revenue and need a smoothed 3-day trailing moving average to damp out day-to-day noise.

Schema

CREATE TABLE sales (
    day     DATE,
    revenue INT
);

day	revenue
2024-05-01	100
2024-05-02	200
2024-05-03	300
2024-05-04	400
2024-05-05	500
2024-05-06	600
2024-05-07	700

Task

For each day return revenue and moving_avg_3d: the average of the current day’s revenue and the two days before it, rounded to 2 decimals. The first two days average whatever rows are available (a partial window), not NULL. Order by day ascending.

Expected output

day	revenue	moving_avg_3d
2024-05-01	100	100.00
2024-05-02	200	150.00
2024-05-03	300	200.00
2024-05-04	400	300.00
2024-05-05	500	400.00
2024-05-06	600	500.00
2024-05-07	700	600.00

Approach

Average revenue over a sliding frame that spans the current row and the two rows before it: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. Ordering by day defines “before,” and because the frame is expressed in rows, the engine averages exactly the observations present — so the first two days simply average fewer values instead of returning NULL. Wrap in ROUND(..., 2) for the required precision.

Query

SELECT
    day,
    revenue,
    ROUND(
        AVG(revenue) OVER (
            ORDER BY day
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ),
        2
    ) AS moving_avg_3d
FROM sales
ORDER BY day;

Walkthrough

2024-05-01: only itself in the frame → AVG(100) = 100.00.
2024-05-02: two rows available → AVG(100, 200) = 150.00.
2024-05-03: full 3-row window → AVG(100, 200, 300) = 200.00.
2024-05-04: window slides to AVG(200, 300, 400) = 300.00.
2024-05-05 → AVG(300, 400, 500) = 400.00; 2024-05-06 → AVG(400, 500, 600) = 500.00; 2024-05-07 → AVG(500, 600, 700) = 600.00.

Complexity & notes

ROWS vs RANGE matters: ROWS BETWEEN 2 PRECEDING ... counts physical rows, giving a true 3-observation window. RANGE BETWEEN '2 days' PRECEDING ... (Postgres interval framing) would instead include every row whose day is within two calendar days, which differs the moment the series has gaps or multiple rows per day.
This is a trailing average (current + past). For a centered 3-day average use ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING.
AVG of an integer column returns numeric in Postgres, so ROUND(x, 2) applies cleanly. In some engines you may need to cast (AVG(revenue::numeric)) to keep decimals.
To require a full window and NULL out the partial early rows, add a guard like CASE WHEN COUNT(*) OVER (... same frame ...) = 3 THEN AVG(...) END.
Add PARTITION BY (e.g. per store) so the moving average restarts per group and never averages across group boundaries. Cost is a single ordered pass, roughly O(n log n) including the sort.