Running Cumulative Spend per Product

MediumSQL

Reported in interviews at Uber

Problem statement

A finance dashboard for an e-commerce site shows, for each product, how its total spend has built up over time. Write the query that feeds it: a running total of spend per product, in chronological order.

Table (SQLite dialect):

  • total_transactions(order_id INT, user_id INT, product_id TEXT, spend REAL, transaction_date TEXT): one row per order; order_id is unique; spend is positive; transaction_date is 'YYYY-MM-DD HH:MM:SS' text.

For every transaction, return product_id, order_id, transaction_date, cumulative_spend, where cumulative_spend is the product's total spend over all of its transactions up to and including this one, in time order. When two transactions of the same product have the same timestamp, take them in increasing order_id, so a row's running total includes only the tied rows with a smaller or equal order_id. Round cumulative_spend to 2 decimals (unrounded values are also accepted). Order the rows by product_id, transaction_date, order_id.

Example

order_id | user_id | product_id | spend | transaction_date
3001     | 11      | P2         | 40.00 | 2026-03-01 10:00:00
3002     | 12      | P1         | 15.50 | 2026-03-01 11:30:00
3003     | 11      | P1         | 20.00 | 2026-03-02 09:00:00
3005     | 13      | P2         | 12.25 | 2026-03-02 14:00:00
3004     | 14      | P2         | 7.75  | 2026-03-02 14:00:00
3006     | 12      | P1         | 4.50  | 2026-03-04 08:15:00

Output:

product_id | order_id | transaction_date    | cumulative_spend
P1         | 3002     | 2026-03-01 11:30:00 | 15.50
P1         | 3003     | 2026-03-02 09:00:00 | 35.50
P1         | 3006     | 2026-03-04 08:15:00 | 40.00
P2         | 3001     | 2026-03-01 10:00:00 | 40.00
P2         | 3004     | 2026-03-02 14:00:00 | 47.75
P2         | 3005     | 2026-03-02 14:00:00 | 60.00

Orders 3004 and 3005 share a timestamp, so 3004 (the smaller id) is added first.

Show hint

Which family of SQL features computes a value for every row from the rows around it, without collapsing the rows?

Relevant roles: Data Scientist and Forward Deployed Engineer.

Related free problems