Menu
CoddyTech

Running Totals

Part of the Connected Data section of Coddy's PostgreSQL journey. Lesson 13 of 25.

An aggregate becomes a window function when followed by OVER. A window frame states which rows contribute to the current row. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW includes all earlier rows plus the current row.

Input table orders:

idcustomer_idamountordered_onstatusmeta
111202025-01-02paid{"channel":"web","items":2,"shipping":{"city":"Haifa"}}
121302025-01-04paid{"channel":"app","items":3,"shipping":{"city":"Haifa"}}
132302025-02-01pending{"channel":"web","items":1}
143502025-02-03paid{"channel":"app","items":5,"shipping":{"city":"Eilat"}}
152102025-02-03paid{"channel":"web","items":2}
SELECT id, SUM(amount) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_amount FROM orders ORDER BY id;

The running amounts are 20, 50, 80, 130 and 140. A unique ordering key makes each step deterministic. Writing the ROWS frame explicitly avoids the default peer-aware frame surprising you when order values tie.

Use an explicit ROWS frame for a row-by-row running total and a stable window order.

challenge icon

Challenge

Medium

Return id, customer_id and customer_running_total, accumulating amounts separately for each customer in id order. Display by id.

The input tables are shown in the test panel. Each run starts from fresh data. Return the named columns in the stated order; do not hard-code the displayed rows.

Try it yourself

-- Write your SQL here.
quiz iconTest yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

All lessons in Connected Data

Practice on your own: PostgreSQL playground