What are common mistakes on SQL: Running Total Per Customer (SUM OVER)?

Filtering on an aggregate in WHERE rather than HAVING. An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders. COUNT(*) versus COUNT(column). The second skips nulls, and that difference is often the entire question.

gitGood.dev

← Back to problems

SQL: Running Total Per Customer (SUM OVER)

MediumDatabases

Description

> This problem is paper-only - see explanation for the canonical SQL solution. Given `Orders(id, customer_id, order_date, total)`, return each row plus a `running_total` column that is the cumulative spend for that customer up to and including the current row, ordered by `(order_date, id)` within each customer. Sort the output by customer_id ascending, order_date ascending, id ascending. **For the auto-grader:** return `[{ id, customer_id, order_date, total, running_total }]`.

Example 1

Input: orders = [{id:1,customer_id:1,order_date:"2026-01-01",total:50},{id:2,customer_id:1,order_date:"2026-01-05",total:75},{id:3,customer_id:2,order_date:"2026-01-02",total:20}]

Output: [{id:1,customer_id:1,order_date:"2026-01-01",total:50,running_total:50},{id:2,customer_id:1,order_date:"2026-01-05",total:75,running_total:125},{id:3,customer_id:2,order_date:"2026-01-02",total:20,running_total:20}]

Constraints

- 0 <= orders.length <= 10^4

SQL: Running Total Per Customer (SUM OVER): approach, complexity and pitfalls

A medium sql problem, graded against 3 test cases (1 of them hidden).

The pattern: SQL

Query-writing problems - joins, aggregation, window functions, and query tuning.

Reach for it when you see: A schema and a question about the data rather than a function signature.

More SQLproblems →

If you are stuck

Show the 2-step hint ladder

SUM(total) OVER (PARTITION BY customer_id ORDER BY order_date, id ROWS UNBOUNDED PRECEDING).
Without ROWS UNBOUNDED PRECEDING, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW which behaves the same UNLESS you have ties on the ORDER BY columns - then RANGE includes all peer rows. Be explicit.

The optimal approach

Show the approach: Canonical SQL:

```sql

SELECT id, customer_id, order_date, total,

SUM(total) OVER (

PARTITION BY customer_id

ORDER BY order_date, id

ROWS UNBOUNDED PRECEDING

) AS running_total

FROM Orders

ORDER BY customer_id, order_date, id;

```

ROWS vs RANGE:

- `ROWS` is positional - exactly N rows back / forward.

- `RANGE` is value-based - includes peers (rows with the same ORDER BY value).

For a running total this difference matters when ORDER BY has ties: `RANGE` will sum all peer rows together, `ROWS` won't. The interviewer might ask "what happens with two orders on the same date?" - know the answer.

The full reference solution in every supported language stays in the editor above - reveal it there once you have had a real attempt.

What the tests check

Read off this problem's own test suite, so these are the cases a submission actually has to survive.

Empty input. At least one case passes an empty collection. Make sure your loop bounds and any "first element" read survive a length of zero.

Common SQL mistakes

These apply to the pattern as a whole, not just this problem.

Filtering on an aggregate in `WHERE` rather than `HAVING`.
An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders.
`COUNT(*)` versus `COUNT(column)`. The second skips nulls, and that difference is often the entire question.