# Two Names on the Ledger > Two accounts. One ledger. Watch the spend stack up. Canonical URL: Domain: SQL · Difficulty: easy · 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. ## 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 | ### users - user_id INTEGER - username TEXT - email TEXT - signup_date TEXT - account_status TEXT - age_bucket TEXT Sample rows (10 of 200): | user_id | username | email | signup_date | account_status | age_bucket | | --- | --- | --- | --- | --- | --- | | 100 | alice | alice@example.com | 2025-02-02 | inactive | 25-34 | | 197 | aaron42 | aaron42@example.com | 2026-03-03 | suspended | 35-44 | | 294 | amelia | amelia@example.com | 2024-04-04 | pending_verification | 45-54 | | 391 | arjun | arjun@example.com | 2025-05-05 | active | 55-64 | | 488 | ava99 | ava99@example.com | 2026-06-06 | inactive | 65+ | | 585 | andrew_k | andrew_k@example.com | 2024-07-07 | suspended | NULL | | 682 | anika | anika@example.com | 2025-08-08 16:34:00 | pending_verification | 18-24 | | 779 | aiden | aiden@example.com | 2026-09-09 14:53:00 | active | 25-34 | | 876 | aria_b | aria_b@example.com | 2024-10-10 | inactive | 35-44 | | 973 | brian | brian@example.com | 2025-11-11 | suspended | 45-54 | ## Expected output First 5 of 26 rows: | transaction_id | username | total_amount | running_total | | --- | --- | --- | --- | | 1067 | aaron42 | 23.46 | 23.46 | | 2005 | alice | 212.04 | 235.5 | | 2072 | aaron42 | 225.51 | 461.01 | | 3010 | alice | 414.09 | 875.0999999999999 | | 3077 | aaron42 | 427.56 | 1302.6599999999999 | ## 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. > **Filter belongs before the window** > > WHERE is evaluated before the window aggregate runs, so once you filter to the two usernames the running SUM OVER (ORDER BY `transaction_id`) accumulates only over those rows. That single ordering inside OVER is what turns a flat total into a row-by-row tally without a self-join or a correlated subquery. ### 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. **Cumulative revenue for two named users** ```sql 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 ``` *Filter to the two users first, then accumulate over `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 pitfall** > > Computing the window over all users and filtering afterward. If the filter lands after the window, the tally already absorbed other users' transactions, so even the rows you keep show inflated totals. Filter in WHERE, before the aggregate sees the data. > **Interviewers watch for** > > Whether you ask one question up front: is the tally combined across both users or separate per user? A candidate who clarifies that, then picks a window function over a grouped sum to preserve the rows, signals they read the output shape rather than pattern-matching on the word 'total'. > **Performance insight** > > On the full 60M-row transactions table the win is the selectivity of the filter: only two of 12M users qualify, so an index on users.username plus `transactions.user_id` turns the scan into a couple of seeks, and the window then sorts just a handful of matched rows by `transaction_id`. The OVER clause never touches the bulk of the table. ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/two_names_on_the_ledger) - [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.