Two Names on the Ledger

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

Amazon

Problem

Finance is reconciling revenue for two customers, alice and aaron42, and wants to see how their spend stacks up across both accounts, one transaction at a time. From the earliest transaction ID to the latest, return each transaction's ID, the customer, its amount, and the combined revenue accumulated through it.

Two accounts. One ledger. Watch the spend stack up.

Tables

transactions

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

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

Expected output

Worked solution and explanation

What this is really testing

Strip off the reconciliation story and this is a cumulative sum scoped to a filtered set of rows. The skill being probed: can you keep every transaction row visible while attaching a tally that grows down the ordered list? Anyone can sum revenue for two users. The trap is collapsing the detail: candidates reach for a grouped SUM, get one number per user, and lose the per-transaction rows the prompt explicitly asks for. The other failure is computing the tally over everybody and only then filtering to alice and aaron42, which leaves the accumulation contaminated by transactions that should never have been in scope.

Building it

Step 1: Join transactions to users

transactions stores only user_id, so you cannot filter on a name directly. An inner join to users on user_id brings username into reach and, being inner, quietly drops any named user who has no transactions.

Step 2: Scope to the two customers

WHERE u.username IN ('alice', 'aaron42') narrows the set first. Doing it here, not after the window, is the whole game: it guarantees the tally sums only these two users' spend.

Step 3: Accumulate without collapsing

SUM(t.total_amount) OVER (ORDER BY t.transaction_id) computes a per-row cumulative figure: each row carries the sum of its own amount plus every earlier transaction_id. Because it is a window aggregate, the original transaction rows survive untouched.

SELECT t.transaction_id,
       u.username,
       t.total_amount,
       SUM(t.total_amount) OVER (ORDER BY t.transaction_id) AS running_total
FROM transactions t
INNER JOIN users u ON t.user_id = u.user_id
WHERE u.username IN ('alice', 'aaron42')
ORDER BY t.transaction_id

Grouped sum (wrong shape)

GROUP BY u.username with SUM(total_amount) returns one row per user. The per-transaction detail and the row-by-row tally the prompt asks for are gone.

Window sum (correct)

SUM(...) OVER (ORDER BY transaction_id) keeps every transaction row and attaches the cumulative figure to each, which is exactly the requested output.

Common follow-up questions

• How would you change the query so each user gets their own independent running tally instead of one combined figure? (Tests whether the candidate knows `PARTITION BY u.username` resets the accumulation per user.)
• If two transactions shared the same `transaction_id`, how would the cumulative figure behave and how would you make it deterministic? (Tests understanding of window ordering ties and the default frame including peers.)
• At billions of rows, which step becomes the bottleneck and what index would you reach for first? (Tests ability to reason about filter selectivity and join cost on `transactions.user_id` at scale.)