Brightlane's finance team needs a strict row-by-row running total of order amounts that accumulates one order at a time even when multiple orders share the same total_amount.
Write a query to return the ID and amount of every order, plus a running total of total_amount that adds exactly one order's amount per row, ordered by id.
Assumptions:
- The
orderstable has one row per order with anidand atotal_amount. - Orders are processed in ascending
idorder. The running total at each row is the combinedtotal_amountof every order at that row'sidor earlier, counted strictly by physical row position rather than by ties on the ordering value. - Two orders with the same
total_amountdo not share a running total — each row contributes its own amount independently to the row-by-row sequence.
Output:
- One row per order, with columns
id,total_amount, andrunning_total.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
id,
total_amount,
SUM(total_amount) OVER (
ORDER BY
id ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS running_total
FROM
ordersThe shape
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW overrides PostgreSQL's default ordered-window frame and forces a strict row-by-row accumulation. Where the default RANGE frame would group tied values into a single peer group and assign every tied row the same running total, the explicit ROWS frame counts physical rows. Each row contributes its own total_amount exactly once, and the running total advances by one row at a time even when two orders share the same amount.
Clause by clause
SELECT id, total_amountreturns each order's identifier and amount. The strict running total is attached.SUM(total_amount) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_totalis the window expression.ORDER BY idsequences the rows. TheROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWclause sets the frame explicitly: include every row from the first one through the current row's physical position, and stop there. The first order'srunning_totalis its own amount. The second order's running total adds the first order's amount once. Each subsequent row adds one more order's amount to the accumulator.FROM ordersreads every order. Every row contributes once to its own running total and to the running totals of all rows that come after it.
Why the explicit ROWS frame instead of the default
The default frame for ORDER BY id inside OVER is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Under RANGE mode, "current row" means every row sharing the current row's ordering value. If two orders had the same id, they would form a peer group and receive the same running total. ROWS mode replaces value equality with physical position: "current row" means this row and no other. The frame boundaries become row positions, not value boundaries, and ties on the ordering expression no longer affect the result.
The trap
On a table where the ordering column is unique, the default frame and the ROWS frame produce the same numbers, which can leave the distinction looking decorative. It is not. The moment ties appear on the ordering expression — same id, same order_date, same total_amount if you ever sort by it — the default frame silently groups them, and the running totals jump in steps larger than one row. The query still runs and the numbers still look plausible. When a strict row-by-row accumulation is what the report needs, write the frame explicitly. The few extra keystrokes are what keep the behavior pinned to physical position rather than to value equality.
You practiced the explicit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame — switch from default RANGE mode to ROWS mode to enforce strict row-by-row accumulation, ignoring peer-group ties on the ordering value.
