Moving Averages and Frame Clauses

A moving average smooths out short-term fluctuations in a time series by averaging a sliding window of recent values. It's one of the core tools in business analytics — used everywhere from 7-day rolling revenue averages to 30-day user retention trends.

Understanding moving averages requires understanding frame clauses — the most nuanced part of window functions.

What is a frame clause?

A frame clause defines exactly which rows are included in the window function calculation relative to the current row. It lives inside OVER() after ORDER BY:

function() OVER (
    PARTITION BY ...
    ORDER BY ...
    ROWS BETWEEN <start> AND <end>    -- this is the frame clause
)

Frame boundaries use these keywords:

7-day moving average

WITH daily_revenue AS (
    SELECT
        o.created_at::DATE               AS day,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.status = 'completed'
    GROUP BY o.created_at::DATE
)
SELECT
    day,
    revenue,
    ROUND(AVG(revenue) OVER (
        ORDER BY day
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2)                               AS moving_avg_7d
FROM daily_revenue
ORDER BY day;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW — includes the current row plus the 6 rows before it: a 7-row window. Note: "rows" means 7 actual rows in the result, not 7 calendar days. If some days have no orders and are absent from the result, the window spans fewer than 7 calendar days. To handle gaps properly, you need to generate a full date scaffold first (covered in the CTEs section).

Centred moving average

A centred average looks both backward and forward — useful when you want to smooth a series without lag:

WITH daily_revenue AS (
    SELECT
        o.created_at::DATE               AS day,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.status = 'completed'
    GROUP BY o.created_at::DATE
)
SELECT
    day,
    revenue,
    ROUND(AVG(revenue) OVER (
        ORDER BY day
        ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
    ), 2)                               AS centred_avg_7d
FROM daily_revenue
ORDER BY day;

3 PRECEDING AND 3 FOLLOWING centres the window on the current row: 3 rows before + current + 3 rows after = 7 rows total. Rows near the start and end of the series use smaller windows automatically (PostgreSQL uses however many rows are available).