The Long Climb

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

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.

Each purchase stacks on the last. Watch it climb.

Tables

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

Expected output

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

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

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.)