Frames and Running Totals

Adding ORDER BY inside OVER changes what the window means. Without it, the window is the whole partition. With it, the window becomes everything up to the current row — which is exactly a running total.

SQL▶ Run
SELECT order_id,
       placed_at,
       amount,
       ROUND(SUM(amount) OVER (ORDER BY placed_at, order_id), 2) AS running_total
FROM orders
ORDER BY placed_at, order_id;

Each row shows the cumulative revenue to that point. Watch the last two rows: a zero-amount order leaves the total unchanged, and the refund at the end pulls it down. A running total is only as sensible as what you feed it.

The default frame, and why it matters

ORDER BY inside OVER silently applies a default frame:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

"From the start of the partition to the current row." That default is what turns SUM into a running total, and it is also why LAST_VALUE misbehaved in the last lesson:

SQL▶ Run
SELECT order_id,
       amount,
       LAST_VALUE(amount) OVER (ORDER BY order_id) AS looks_wrong,
       LAST_VALUE(amount) OVER (
         ORDER BY order_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS actually_last
FROM orders
ORDER BY order_id;

The first column returns each row's own amount — because with the default frame, the last row of the window is the current row. Only with an explicit frame covering the whole partition does LAST_VALUE return the final value.

ROWS versus RANGE

Both define the frame; they count differently.

• ROWS counts physical rows. 2 PRECEDING means the two rows before this one.
• RANGE works on values. All rows with the same ORDER BY value are peers and are included together.

With no ties the two are identical. With ties they are not:

SQL▶ Run
SELECT first_name,
       salary,
       SUM(salary) OVER (ORDER BY salary
                         RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS by_range,
       SUM(salary) OVER (ORDER BY salary
                         ROWS  BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS by_rows
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary, by_rows;

Two employees earn 88,000. RANGE gives them both the same running total, because they are peers and are added together. ROWS adds them one at a time, so the first of the pair shows a total that is 88,000 lower — and which of the two comes first is arbitrary.

For a running total over a column with duplicates, RANGE is usually what you mean. Since RANGE is the default, most people get it without knowing.

Writing frames explicitly

ROWS BETWEEN <start> AND <end>

where each bound is one of UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING, UNBOUNDED FOLLOWING.

A three-row moving average:

SQL▶ Run
SELECT order_id,
       amount,
       ROUND(AVG(amount) OVER (
         ORDER BY order_id
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 2) AS moving_avg_3
FROM orders
ORDER BY order_id;

The first row averages one value, the second averages two, and from the third on it averages three. The window is not "the last three rows" so much as "at most the last three" — there is nothing before the start of the partition.

Centred instead of trailing:

SQL▶ Run
SELECT order_id,
       amount,
       ROUND(AVG(amount) OVER (
         ORDER BY order_id
         ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
       ), 2) AS centred_avg
FROM orders
ORDER BY order_id;

Running totals within groups

PARTITION BY restarts the accumulation:

SQL▶ Run
SELECT customer_id,
       order_id,
       amount,
       ROUND(SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_id), 2)
         AS running_for_customer
FROM orders
ORDER BY customer_id, order_id;

Each customer's total starts again from zero — the shape behind "cumulative spend per account", "balance after each transaction", and most statement-style reports.

A percentage-of-total in one pass

Two windows in one query, one framed and one not:

SQL▶ Run
SELECT order_id,
       amount,
       ROUND(SUM(amount) OVER (ORDER BY order_id), 2)                     AS running,
       ROUND(SUM(amount) OVER (), 2)                                      AS grand_total,
       ROUND(100.0 * SUM(amount) OVER (ORDER BY order_id)
                   / SUM(amount) OVER (), 1)                              AS pct_complete
FROM orders
ORDER BY order_id;

OVER () has no ORDER BY, so it is the whole partition — the grand total. OVER (ORDER BY order_id) accumulates. Both in one pass, no subquery.

Common mistakes

• LAST_VALUE with no explicit frame — returns the current row.
• Assuming ORDER BY in OVER is only about sorting — it defines the frame, and therefore the answer.
• ROWS where RANGE was meant — tied values are split arbitrarily.
• n PRECEDING with RANGE on a text column — RANGE offsets need a numeric or date type.

Interview question

Write a running total of order amounts by date.

SUM(amount) OVER (ORDER BY placed_at). The follow-up that separates answers: what frame is that using? The default, RANGE UNBOUNDED PRECEDING AND CURRENT ROW — and knowing that explains both the running total and why LAST_VALUE looks broken.

Check yourself

1. What frame does OVER (ORDER BY x) use by default?
2. Why does LAST_VALUE usually return the current row?
3. When do ROWS and RANGE produce different results?