The Weight of Everything Before

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

mid

Asked in

Databricks

Problem

An analyst building lifetime-value curves wants every purchase in the transactions table tagged with how much that customer had spent so far, counting every purchase of theirs dated on or before it. Return one row per purchase with the customer, the purchase date and that spend to date, customers in id order and each one's purchases from earliest to latest.

Every purchase carries the ones that came before it. Trace the 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 really is

Strip off the lifetime-value framing and this is a per-customer running sum that has to keep every purchase row. A GROUP BY user_id with SUM(total_amount) is the simpler tool and fails on sight: 200 purchases collapse into 15 customer rows, each holding one lifetime number, and the trajectory is gone. The trap that actually separates candidates is subtler. Many reach for a window, write SUM(total_amount) OVER (PARTITION BY user_id), see 200 rows and stop. With no ordering inside the window, every row carries the customer's final total, so a first purchase already shows everything that customer will ever spend.

Break down the requirements

Step 1: Keep every purchase row

The output has one row per purchase, not one per customer. That rules out a GROUP BY collapse: you need a value computed alongside each existing row while the row count stays at 200.

Step 2: Restart the sum for each customer

Partition by user_id so one customer's spend never bleeds into another's. Without it you get a single company-wide running sum, which looks plausible for the first customer and wrong for every customer after.

Step 3: Order the window by transaction_date

The ordering inside OVER is what makes the sum cumulative. The default RANGE frame then includes every row up to and including the current date, so two purchases on the same day share one value: the spend through that day. The final ORDER BY user_id, transaction_date lays each history out chronologically.

The solution

SELECT user_id, transaction_date, SUM(total_amount) OVER (PARTITION BY user_id ORDER BY transaction_date) AS running_total FROM transactions ORDER BY user_id, transaction_date;

Common follow-up questions

• How would you show the spend across only each customer's last three purchases, a moving window rather than a cumulative one? (Tests switching to an explicit `ROWS BETWEEN 2 PRECEDING AND CURRENT ROW` frame.)
• If two purchases share the same `transaction_date` for a customer, what `running_total` does each row show, and how would you make the accumulation strictly row by row? (Tests `RANGE` versus `ROWS` framing and a deterministic tiebreaker such as `transaction_id`.)