How do you compute a running total or moving average in SQL?

Running total

SELECT
    sale_date,
    amount,
    SUM(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM daily_sales;

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW means "every row from the very start of the partition up through the current row" — the textbook definition of a running total. In most engines, simply adding ORDER BY inside OVER (...) with SUM() and no explicit frame defaults to exactly this frame anyway, but writing it explicitly is clearer and avoids relying on an implicit default that some engines define differently.

Moving average over a fixed window (e.g., 7-day)

SELECT
    sale_date,
    amount,
    AVG(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7day
FROM daily_sales;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW means "the current row plus the 6 rows before it" — 7 rows total, a rolling 7-period average. Note this counts rows, not calendar days — if daily_sales has a missing date (no row for that day), the "7 preceding rows" span more than 7 calendar days. Use RANGE instead of ROWS with an actual interval (RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW, supported by engines with proper RANGE-with-interval support) if you need true calendar-based windows regardless of gaps.

Running total per group

SELECT
    customer_id, order_date, amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS customer_running_total
FROM orders;

PARTITION BY customer_id resets the running total independently for each customer — critical when the running calculation should be scoped per group rather than across the whole table.

ROWS vs RANGE — the subtle distinction

ROWS counts physical rows in the frame; RANGE (with ORDER BY) counts logical peer groups — rows with the same ORDER BY value are treated as a single unit, which matters if your ORDER BY column has ties (e.g., multiple sales on the exact same timestamp) and you want them all included or excluded together rather than split arbitrarily by physical row order.

Why this beats the pre-window-function alternative

Before window functions, a running total required a correlated subquery re-summing from the start on every row (SELECT SUM(amount) FROM t t2 WHERE t2.date <= t.date) — an O(n²) pattern for n rows, since each row triggers its own full re-scan. A window function computes this in a single pass with O(n log n) or better, depending on the engine's implementation — a meaningful, measurable performance difference on large tables.