Compute a deterministic running order total
Return each order and the cumulative amount across all orders by date then ID. Amounts are non-NULL and dates may tie. Return only the requested columns and follow the specified ordering. This question probes an explicit rows frame and unique id tiebreaker give a row-by-row cumulative sum rather than peer-group accumulation.
Input
Setup script defining customers(id INT, name VARCHAR(30)); orders(id INT, customer_id INT, amount INT, order_date VARCHAR(10)).
Output
id, order_date, amount, running_total; ascending order_date then id.
Constraints
0–10000 rows per table. IDs are unique and non-NULL unless stated otherwise; foreign keys may be NULL. Numeric amounts are integers from 0 to 1000000. Dates are valid YYYY-MM-DD text. Text uses case-sensitive comparison; test data has consistent case. Duplicate business values and NULLs are allowed unless excluded by the task.
Example
Tables and sample rows
CREATE TABLE customers(id INT, name VARCHAR(30)); INSERT INTO customers VALUES (1,'Ana'),(2,'Ben'),(3,'Cara'); CREATE TABLE orders(id INT, customer_id INT, amount INT, order_date VARCHAR(10)); INSERT INTO orders VALUES (1,1,100,'2024-01-01'),(2,1,200,'2024-02-01'),(3,2,100,'2025-01-01'),(4,NULL,50,'2024-12-31');
Expected result
id | order_date | amount | running_total 1 | 2024-01-01 | 100 | 100 2 | 2024-02-01 | 200 | 300 4 | 2024-12-31 | 50 | 350 3 | 2025-01-01 | 100 | 450
Another dataset (1)
CREATE TABLE customers(id INT, name VARCHAR(30)); INSERT INTO customers VALUES (1,'Solo'),(2,'None'); CREATE TABLE orders(id INT, customer_id INT, amount INT, order_date VARCHAR(10)); INSERT INTO orders VALUES (1,1,0,'2023-12-31'),(2,NULL,10,'2024-01-01');
Expected result
id | order_date | amount | running_total 1 | 2023-12-31 | 0 | 0 2 | 2024-01-01 | 10 | 10
Covers missing matches, ties, or NULL values without relying on insertion order.
Hints (2)
- Sort by date and unique ID.
- Specify an explicit ROWS frame.
Target complexity: time O(n log n) · space O(n)