Tables customers(id int primary key, name text, city text) and orders(id int primary key, customer_id int references customers(id), amount int, ordered_on date).
For every order, show customer_id, ordered_on, amount and running_total — the sum of that customer's order amounts up to and including this order, taking orders by date and then by order id. Order the output by customer_id, ordered_on, id.
Constraints
- Write one SELECT statement for PostgreSQL 16.
- Output columns and row order must match exactly.
Input and output
- Input
- The setup script in the test case creates and fills the tables described above.
- Output
- Columns: customer_id, ordered_on, amount, running_total.
Example
Input
create table customers (id int primary key, name text not null, city text not null);
create table orders (id int primary key, customer_id int not null references customers(id), amount int not null, ordered_on date not null);
insert into customers values (1,'Riya','Pune'),(2,'Dev','Delhi');
insert into orders values (1,1,100,'2025-01-05'),(2,2,40,'2025-01-06'),(3,1,50,'2025-01-07'),(4,1,30,'2025-01-07');Output
1|2025-01-05|100|100
1|2025-01-07|50|150
1|2025-01-07|30|180
2|2025-01-06|40|40Riya's totals grow 100 → 150 → 180; orders 3 and 4 share a date and are added in id order.
Show hints
- SUM can be used as a window function with PARTITION BY and ORDER BY.
- Add the order id to the window ORDER BY, or use ROWS frames, so same-day orders are added one at a time.
Show the explanation
sum(amount) over (partition by customer_id order by ordered_on, id) restarts the sum for each customer and accumulates in the given order. The default frame with an ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which would treat rows that tie on the ORDER BY keys as one step; including id (unique) avoids ties, or write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explicitly.
Original UniCircle question. Last reviewed 22 Oct 2026.