# The Long Climb > Each purchase stacks on the last. Watch it climb. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem The finance team wants to track each customer's spending trajectory over time. For every transaction, show it alongside that customer's cumulative total spend up to and including that row, breaking same-day ties by transaction_id, with each customer's oldest transactions first. ## 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 you collapse each customer down to one line, losing the transaction-by-transaction detail the report needs. So it has to be a windowed SUM that walks each customer's timeline while every row survives. The trap hides in the ordering. Two purchases landing on the same day have no natural sequence, and if you order the window by date alone the cumulative values for those rows come back in whatever order the engine feels like that run. The same query then prints different numbers on different runs. Settle the tie with transaction_id and the climb becomes deterministic. --- ### Break down the requirements #### Step 1: Keep every transaction Read each transaction from the transactions table (user_id, product_id, total_amount, transaction_date). No aggregation yet: one output row per input row. #### Step 2: Accumulate per customer Compute the accumulating total of total_amount with SUM() OVER a window scoped PARTITION BY user_id, so each customer's climb is 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 makes same-day rows deterministic instead of engine-dependent. --- ### 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 ``` > **Cost Analysis** > > A window SUM over 250M rows partitions by user_id, then sorts each partition by (transaction_date, transaction_id). With millions of users the per-partition sorts spread out nicely, but the final ORDER BY still forces a global sort over the full output. On this volume that sort is the bottleneck: watch work_mem so it does not spill to disk, and lean on parallel workers per partition. > **Interviewers watch for** > > 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, so same-day purchases all jump to the same cumulative value and lose their individual progression. Adding transaction_id to the ORDER BY breaks the peers apart. > **Common pitfall** > > If you truly want strict transaction-by-transaction progression even when the ORDER BY still has peers, spell out ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With a unique tiebreak like transaction_id there are no peers left, so RANGE and ROWS agree, 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 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.