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:
id | customer_id | amount | ordered_on | status | meta |
|---|---|---|---|---|---|
11 | 1 | 20 | 2025-01-02 | paid | {"channel":"web","items":2,"shipping":{"city":"Haifa"}} |
12 | 1 | 30 | 2025-01-04 | paid | {"channel":"app","items":3,"shipping":{"city":"Haifa"}} |
13 | 2 | 30 | 2025-02-01 | pending | {"channel":"web","items":1} |
14 | 3 | 50 | 2025-02-03 | paid | {"channel":"app","items":5,"shipping":{"city":"Eilat"}} |
15 | 2 | 10 | 2025-02-03 | paid | {"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
MediumReturn 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.
This lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in Connected Data
3Window Calculations
Number Rows in GroupsRanks and TiesRunning TotalsCompare with LAGLatest Order per CustomerPractice on your own: PostgreSQL playground