Running Total Revenue per Customer

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Description

You are given a table of customer purchases. Each purchase has a customer ID, an amount, and a date. Write a SQL query that returns every purchase row enriched with a running_total column: the cumulative sum of amount for that customer up to and including the current row, ordered by purchase_date. Return all columns (id, customer_id, amount, purchase_date, running_total), ordered by customer_id then purchase_date. Table: Purchases

Column Name	Type	Description
id	INT	Primary key
customer_id	INT	ID of the customer
amount	INT	Purchase amount
purchase_date	DATE	Date of the purchase

Database Schema (Inferred)

Purchases

Column Name	Example Value
id	1
customer_id	1
amount	100
purchase_date	2023-01-05

Example

Purchases

id	customer_id	amount	purchase_date
1	1	100	2023-01-05
2	1	200	2023-02-10
3	1	150	2023-03-15
4	2	300	2023-01-20
5	2	50	2023-04-01
6	3	500	2023-05-05

Output

id	customer_id	amount	purchase_date	running_total
1	1	100	2023-01-05	100
2	1	200	2023-02-10	300
3	1	150	2023-03-15	450
4	2	300	2023-01-20	300
5	2	50	2023-04-01	350
6	3	500	2023-05-05	500

Explanation:

Use SUM() as a window function partitioned by customer_id and ordered by purchase_date with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Approach hint

Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.

Common mistake

Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.