SQL running totals and moving averages: understanding window frames

Calculating across a sequence of rows

In the previous article, we learned that window functions calculate across related rows without collapsing those rows. Now we will use that idea for calculations that change as we move through an ordered sequence. Consider the same orders data, with the order amount shown for clarity:

Suppose the requirement is show every order and the total order value accumulated up to that point. For the first order the value is 1500, for the second 1500 + 3200 = 4700, for the third 1500 + 3200 + 3600 = 8300. The calculation keeps growing as we move through the ordered rows. This is a running total, also called a cumulative sum:

Every original order remains. The calculation simply uses a different set of rows for each output row. That set of rows is controlled by a window frame.

Adding a frame to an aggregate

An ordinary aggregate, SELECT SUM(order_amount) FROM orders, produces one total (21150); the rows have been aggregated into a single result. Now add OVER, and SUM becomes a window calculation that can calculate across related rows while preserving every input row. For a running total:

SELECT
    order_id,
    ordered_at,
    order_amount,
    SUM(order_amount) OVER (
        ORDER BY ordered_at, order_id
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW
    ) AS running_total
FROM orders
ORDER BY
    ordered_at,
    order_id;

The important part is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This defines the window frame.

What is a window frame?

We already know that OVER (ORDER BY ordered_at) puts rows into an analytical order. A window frame goes one step further and asks: for the current row, exactly which rows from that ordered sequence should participate in this calculation? Consider order 1004. The frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW includes 1001, 1002, 1003, 1004, and not 1005 or 1006. When SQL moves to order 1005, the frame becomes 1001 through 1005. The frame moves with the current row. That is the core idea behind running and rolling calculations.

Reading a window frame

A frame usually has BETWEEN frame_start AND frame_end. In ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW:

UNBOUNDED PRECEDING   Start at the first row in the partition.
CURRENT ROW           Stop at the current row.

So it means include every row from the beginning through the current row, which is exactly what a cumulative total requires. Step by step, order 1001 has a frame of just 1500; 1002 adds to 4700; 1003 to 8300; 1004 to 16800. The window keeps expanding, which is why the calculation is cumulative.

Running totals within groups

Sometimes the running total should restart for each group. To show the cumulative order value separately for each customer, Asha’s running total should not include Ben’s orders. Use PARTITION BY:

SELECT
    order_id,
    customer,
    ordered_at,
    order_amount,
    SUM(order_amount) OVER (
        PARTITION BY customer
        ORDER BY ordered_at, order_id
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW
    ) AS customer_running_total
FROM orders
ORDER BY
    customer,
    ordered_at,
    order_id;

For Asha, 1500 then 1500 + 750 = 2250; for Ben, 3200 then 3200 + 3600 = 6800; then the calculation restarts for Carla and Dev.

PARTITION BY   Which rows belong to the same independent calculation?
ORDER BY       In what sequence should those rows be processed?
ROWS BETWEEN   Which rows around the current row participate?

Moving averages

A running total grows from the beginning of the data. A moving average usually looks at only a limited number of nearby rows. To calculate, for each order, the average order amount across that order and the previous two orders (a three-row moving average):

SELECT
    order_id,
    ordered_at,
    order_amount,
    AVG(order_amount) OVER (
        ORDER BY ordered_at, order_id
        ROWS BETWEEN 2 PRECEDING
                 AND CURRENT ROW
    ) AS moving_avg_3_orders
FROM orders
ORDER BY
    ordered_at,
    order_id;

What does 2 PRECEDING mean?

For order 1004, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW contains 1002, 1003, 1004, because it includes 2 previous rows plus the current row (3 rows). So the average is (3200 + 3600 + 8500) / 3 = 5100. When SQL moves to order 1005, the frame shifts to 1003, 1004, 1005, and the average becomes (3600 + 8500 + 750) / 3 = 4283.33. This is why the calculation is called moving or rolling: the frame moves through the ordered rows.

What happens at the beginning?

For the first row, there are no two previous rows, and SQL does not invent them. For order 1001, the frame contains only 1001, so the moving average is 1500. For order 1002, only two rows are available, so (1500 + 3200) / 2 = 2350. Starting from order 1003, the full three-row frame becomes available.

Three rows does not mean three days

Consider AVG(order_amount) OVER (ORDER BY ordered_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW). Someone describes this as “the three-day moving average.” That is incorrect: ROWS counts rows, not calendar days. For order 1004 on July 31, the three-row window is July 2 (order 1002), July 15 (order 1003), and July 31 (order 1004). Those rows span almost a month, yet ROWS BETWEEN 2 PRECEDING AND CURRENT ROW still treats them as a three-row frame.

AVG(order_amount) OVER (
    ORDER BY ordered_at
    ROWS BETWEEN 2 PRECEDING
             AND CURRENT ROW
)

-- A: the current calendar day and previous two calendar days
-- B: the current row and previous two rows in the window ordering
-- C: every row from the previous 48 hours
-- D: the current month

Rows and time are different business questions

For “last three orders,” a row-based frame makes sense (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW). For “previous three days,” the frame must be defined using time values rather than the number of rows. Some databases support value-based RANGE frames such as PostgreSQL-style RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW when ordering by a compatible timestamp. Exact support and syntax vary across databases.

Understanding ROWS

ROWS defines the frame according to physical positions in the analytical ordering:

ROWS BETWEEN CURRENT ROW AND CURRENT ROW          one row
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW          at most two rows
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW          at most three rows
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  the cumulative pattern

What about FOLLOWING?

Frames can also include rows after the current row. ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING includes the previous row, current row, and next row, which can be useful for centered calculations. But be careful when using future rows in business metrics: a model or report that is supposed to represent information known at the current point in time should not accidentally include future observations. For cumulative and historical metrics, frames normally end at CURRENT ROW.

ROWS vs RANGE

Two important frame types are ROWS and RANGE, and they should not be treated as interchangeable. ROWS works with positions in the ordered result: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means two previous rows plus this row. RANGE works with values related to the ORDER BY expression, and depending on the database and frame definition, it can include rows whose ordering values fall within a particular range or treat equal ordering values as peers.

ROWS    Think about positions.
RANGE   Think about ordering values.

For beginner SQL, start with explicit ROWS frames whenever the business requirement is defined in rows. Use RANGE only when the requirement genuinely depends on the ordering value and you understand your database’s frame semantics.

Gotcha: do not rely blindly on the default frame

Gotcha: running calculations need deterministic ordering

Suppose two orders have exactly the same ordered_at and you order only by ordered_at. Which tied order comes first? The query has not said, and for a row-based running total that can change the intermediate cumulative values assigned to those rows. If each order has a unique order_id, add it as a tie-breaker (ORDER BY ordered_at, order_id) to produce a deterministic sequence.

Moving calculations within each customer

PARTITION BY can also be combined with rolling frames. To calculate, for each customer, the average of their current and previous order, use PARTITION BY customer with ROWS BETWEEN 1 PRECEDING AND CURRENT ROW. For Asha (1500 then 750), the averages are 1500 and (1500 + 750) / 2 = 1125. For Ben (3200 then 3600), they are 3200 and (3200 + 3600) / 2 = 3400. The frame never crosses from Asha into Ben because PARTITION BY customer creates independent analytical sequences.

Running count

Window frames are not limited to SUM and AVG. To answer how many orders have occurred up to each point, use COUNT(*) over the same cumulative frame:

SELECT
    order_id,
    ordered_at,
    COUNT(*) OVER (
        ORDER BY ordered_at, order_id
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW
    ) AS orders_so_far
FROM orders
ORDER BY
    ordered_at,
    order_id;

The same frame can support a running total, a running count, and a running average. The aggregate function changes; the frame definition can remain the same.

Cumulative vs rolling

A cumulative average (AVG(order_amount) over ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) asks “what is the average across all orders from the beginning through this order?” A rolling window (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) asks “what is the average across only the current and previous two rows?” The window function is the same; the frame changes the calculation.

Cumulative   frame begins at the start and grows:
             [1] [1,2] [1,2,3] [1,2,3,4] ...

Rolling      frame has a bounded size and moves:
             [1] [1,2] [1,2,3] [2,3,4] [3,4,5] ...

How to spot running and rolling windows

Think about running or rolling windows when the requirement says:

Watch for these common mistakes:

• A normal aggregate collapses rows when individual rows should remain.
• ROWS BETWEEN 2 PRECEDING is described as three days.
• PARTITION BY is missing, so one customer’s values flow into another’s calculation.
• The ordering does not match the business sequence.
• Tied ordering values have no deterministic tie-breaker.
• The default window frame is relied upon without understanding it.
• Future rows are included when only information available so far should be used.

Answer four questions for a running or rolling calculation

One more prediction

Consider this ordered data with the window expression AVG(order_amount) OVER (ORDER BY row ROWS BETWEEN 2 PRECEDING AND CURRENT ROW). What values participate in the average for row 4?

AVG(order_amount) OVER (
    ORDER BY row
    ROWS BETWEEN 2 PRECEDING
             AND CURRENT ROW
)

-- A: 100, 200, 300, 400
-- B: 200, 300, 400
-- C: 300, 400
-- D: 400

Summary

Running and rolling calculations use window functions to calculate across an ordered set of rows without collapsing those rows. A cumulative sum commonly uses SUM(order_amount) OVER (ORDER BY ordered_at, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), meaning from the beginning of the sequence through the current row. A three-row moving average uses ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, meaning the previous two rows plus the current row. PARTITION BY lets the calculation restart for each group.

Next, we will use analytical SQL to compare one row with earlier or later rows using LAG, LEAD, and first or last value patterns.