# The Long Climb

> Each purchase stacks on the last. Watch it climb.

Canonical URL: <https://datadriven.io/problems/the_long_climb>

Domain: SQL · Difficulty: medium · Seniority: L4 · Asked in: Goldman Sachs

## Problem

Every customer shows up as one lifetime spend figure in the finance team's review, and nobody can see how that figure built up purchase by purchase. From the transactions table, list every purchase beside that customer's cumulative spend up to and including it, customers by ascending user_id and each customer's purchases oldest first, with same-day purchases taken in transaction_id order.

## Tables

### transactions

- transaction_id INTEGER
- user_id INTEGER
- product_id INTEGER
- quantity INTEGER
- total_amount REAL
- transaction_date TEXT

Sample rows (10 of 200):

| transaction_id | user_id | product_id | quantity | total_amount | transaction_date |
| --- | --- | --- | --- | --- | --- |
| 1067 | 197 | 1001 | 2 | 23.46 | 2026-02-02 |
| 1134 | 294 | 1050 | 3 | 36.93 | 2026-03-03 |
| 1201 | 391 | 1099 | 4 | 50.4 | 2026-04-04 |
| 1268 | 488 | 1148 | 5 | 63.87 | 2026-05-05 |
| 1335 | 585 | 1197 | 0 | 0 | 2026-06-06 |
| 1402 | 682 | 1246 | 2 | 90.81 | 2026-07-07 |
| 1469 | 779 | 1295 | 3 | 104.28 | 2026-08-08 |
| 1536 | 876 | 1344 | 4 | 117.75 | 2026-09-09 |
| 1603 | 973 | 1393 | 5 | -45 | 2026-01-10 |
| 1670 | 1070 | 1442 | 1 | 144.69 | 2026-02-11 |

## Expected output

First 5 of 200 rows:

| user_id | product_id | total_amount | transaction_date | cumulative_sales |
| --- | --- | --- | --- | --- |
| 100 | 5362 | 1222.29 | 2026-01-07 | 1222.29 |
| 100 | 3157 | 616.14 | 2026-01-18 | 1838.4299999999998 |
| 100 | 2422 | 414.09 | 2026-03-03 | 2252.52 |
| 100 | 4627 | 1020.24 | 2026-03-20 | 3272.7599999999998 |
| 100 | 2422 | 414.09 | 2026-04-03 | 3686.85 |

## Worked solution and explanation

### What this problem really is

Strip off the finance framing and this is a per-customer prefix sum that has to keep every original row. That last part is the whole game: reach for a `GROUP BY` and each customer collapses to one line, losing the purchase-by-purchase climb the report needs. So it has to be a windowed `SUM` that walks each customer's timeline while every row survives. The trap is in the ordering. If you order the window by `transaction_date` alone, two purchases on the same day become peers. The default frame then gives both of them the total through the end of that day, so two steps in the climb flatten into one. Settle the tie with `transaction_id` and every row gets its own step.

---

### Break down the requirements

#### Step 1: Keep every transaction

Read each row from `transactions` (`user_id`, `product_id`, `total_amount`, `transaction_date`). Do not aggregate: the output keeps one row per input row.

#### Step 2: Accumulate per customer

Compute the accumulating total of `total_amount` with `SUM()` over a window scoped by `PARTITION BY user_id`. Each customer's climb is then independent and never picks up another user's spend.

#### Step 3: Order the window deterministically

Order the window by `transaction_date`, `transaction_id`. The date makes the sum run up to and including the current row. `transaction_id` is the tiebreak that gives same-day rows separate steps instead of a shared value.

#### Step 4: Pin the output order

Sort the output the same way: `ORDER BY user_id, transaction_date, transaction_id`. `transaction_id` is not in the output, but it still decides the sequence of same-day rows, so the result order is fully fixed.

---

### The solution

**Partitioned running total**

```sql
SELECT
    user_id,
    product_id,
    total_amount,
    transaction_date,
    SUM(total_amount) OVER (
        PARTITION BY user_id
        ORDER BY transaction_date, transaction_id
    ) AS cumulative_sales
FROM transactions
ORDER BY user_id, transaction_date, transaction_id
```

> **One sort can serve both orders**
>
> The window needs each customer's rows sorted by (`transaction_date`, `transaction_id`) within `user_id`, and the final `ORDER BY` asks for exactly that sequence. A good planner therefore sorts once and streams the running sum off the result. At 250M rows that single sort is the whole cost: give it enough `work_mem` that it does not spill, and let parallel workers take separate customers.

> **Same-day purchases must not share a total**
>
> The tell is ordering the window by `transaction_date` alone. The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which treats all peer rows (same date) as one group. Same-day purchases then all jump to the same cumulative value and lose their individual progression. Adding `transaction_id` to the window's `ORDER BY` breaks the peers apart.

> **`ROWS` states the intent `RANGE` only implies**
>
> Spelling out ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW gives strict row-by-row progression even when the order keys still have peers. With a unique tiebreak like `transaction_id` no peers remain, so RANGE and ROWS agree here, but stating ROWS documents the intent.

---

## Common follow-up questions

- How would you handle multiple transactions on the same date for one user? _(Tests ROWS vs RANGE frame semantics.)_
- What if you needed the cumulative total as a percentage of their lifetime total? _(Divide by `SUM(total_amount) OVER (PARTITION BY user_id)` to get the percentage.)_
- How would this perform on 250M rows? _(The partitioned sort is the bottleneck. Discusses `work_mem` tuning and parallel query execution.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the_long_climb)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.