Window Functions

Running Total

Accumulating Over Order

A window aggregate can show the cumulative value at each row without collapsing rows.

Program

Play the query to compute a running payment total from a selectable start day.

start_day
running_total.sql
Replay: real traced execution (multi-file project)
CREATE TABLE payments (day INTEGER, amount INTEGER);
INSERT INTO payments VALUES (1, 5), (2, 7), (3, 4);
WITH params(start_day) AS (VALUES (1)) SELECT day, amount, SUM(amount) OVER (ORDER BY day) AS running_total FROM payments WHERE day >= (SELECT start_day FROM params) ORDER BY day;

CREATE TABLE payments (day INTEGER, amount INTEGER);
INSERT INTO payments VALUES (1, 5), (2, 7), (3, 4);
WITH params(start_day) AS (VALUES (2)) SELECT day, amount, SUM(amount) OVER (ORDER BY day) AS running_total FROM payments WHERE day >= (SELECT start_day FROM params) ORDER BY day;

CREATE TABLE payments (day INTEGER, amount INTEGER);
INSERT INTO payments VALUES (1, 5), (2, 7), (3, 4);
WITH params(start_day) AS (VALUES (3)) SELECT day, amount, SUM(amount) OVER (ORDER BY day) AS running_total FROM payments WHERE day >= (SELECT start_day FROM params) ORDER BY day;

  1. payments ← 0 rows

    1CREATE TABLE payments (day INTEGER, amount INTEGER);2INSERT INTO payments VALUES (1, 5), (2, 7), (3, 4);
    values this step0 rowspayments

  2. payments ← 3 rows

    1CREATE TABLE payments (day INTEGER, amount INTEGER);2INSERT INTO payments VALUES (1, 5), (2, 7), (3, 4);3WITH params(start_day) AS (VALUES (1)) SELECT day, amount, SUM(amount) OVER (ORDER BY day) AS running_total FROM payments WHERE day >= (SELECT start_day FROM params) ORDER BY day;
    values this step3 rowspayments

  3. result ← 3 rows

    2INSERT INTO payments VALUES (1, 5), (2, 7), (3, 4);3WITH params(start_day) AS (VALUES (1)) SELECT day, amount, SUM(amount) OVER (ORDER BY day) AS running_total FROM payments WHERE day >= (SELECT start_day FROM params) ORDER BY day;
    values this step3 rowsresult

  1. payments ← 0 rows

    1CREATE TABLE payments (day INTEGER, amount INTEGER);2INSERT INTO payments VALUES (1, 5), (2, 7), (3, 4);
    values this step0 rowspayments

  2. payments ← 3 rows

    1CREATE TABLE payments (day INTEGER, amount INTEGER);2INSERT INTO payments VALUES (1, 5), (2, 7), (3, 4);3WITH params(start_day) AS (VALUES (2)) SELECT day, amount, SUM(amount) OVER (ORDER BY day) AS running_total FROM payments WHERE day >= (SELECT start_day FROM params) ORDER BY day;
    values this step3 rowspayments

  3. result ← 2 rows

    2INSERT INTO payments VALUES (1, 5), (2, 7), (3, 4);3WITH params(start_day) AS (VALUES (2)) SELECT day, amount, SUM(amount) OVER (ORDER BY day) AS running_total FROM payments WHERE day >= (SELECT start_day FROM params) ORDER BY day;
    values this step2 rowsresult

  1. payments ← 0 rows

    1CREATE TABLE payments (day INTEGER, amount INTEGER);2INSERT INTO payments VALUES (1, 5), (2, 7), (3, 4);
    values this step0 rowspayments

  2. payments ← 3 rows

    1CREATE TABLE payments (day INTEGER, amount INTEGER);2INSERT INTO payments VALUES (1, 5), (2, 7), (3, 4);3WITH params(start_day) AS (VALUES (3)) SELECT day, amount, SUM(amount) OVER (ORDER BY day) AS running_total FROM payments WHERE day >= (SELECT start_day FROM params) ORDER BY day;
    values this step3 rowspayments

  3. result ← 1 row

    2INSERT INTO payments VALUES (1, 5), (2, 7), (3, 4);3WITH params(start_day) AS (VALUES (3)) SELECT day, amount, SUM(amount) OVER (ORDER BY day) AS running_total FROM payments WHERE day >= (SELECT start_day FROM params) ORDER BY day;
    values this step1 rowresult

Follow the Window

  1. The default start_day is 1.
  2. The filter keeps day 1, day 2, and day 3.
  3. The window walks those rows in day order.
  4. Each running total adds the current amount to the earlier kept amounts. | day | amount | running total | | --- | --- | --- | | 1 | 5 | 5 | | 2 | 7 | 12 | | 3 | 4 | 16 |
window aggregate `SUM(amount) OVER (...)` keeps one output row per input row.
running total `ORDER BY day` makes each row include the previous days in order.
filter first The selectable start day changes which rows enter the window.

Exercise: running_total.sql

Reproduce the running totals 5, 12, and 16, then use the pinned start_day variants to predict the totals when the first kept day is 2 or 3.