# Average Spending by Account Status > Average per-user lifetime spending segmented by account status Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 · Asked in: Rockerbox ## Problem Users who never made a purchase stay out of finance's lifetime value comparison, which weighs account statuses against each other only across actual buyers. Using the users and transactions tables, show each account status with the average lifetime spend of its buyers, a buyer's lifetime spend being everything they have paid across all of their transactions. ## Tables ### 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 | ### 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 | ## Expected output First 4 of 4 rows: | account_status | avg_total_spending | | --- | --- | | active | 9032.38 | | inactive | 9060.48 | | pending_verification | 9429.165 | | suspended | 9183.93 | ## Worked solution and explanation ### Why this problem exists in real interviews LTV questions like this probe one habit specifically: do you name the **unit of observation** before writing SQL? Finance wants average spend per user, not per transaction. A whale with 1000 small purchases otherwise dominates a casual buyer with one big one. The interviewer is watching to see if you collapse to per-user totals in a subquery before averaging. --- ### Break down the requirements #### Step 1: Collapse transactions to one row per user Subquery on `transactions` with `SUM(total_amount) AS total_spent GROUP BY user_id`. This is the unit of observation the outer average should run over. Pre-aggregating here is what makes the final `AVG` user-weighted instead of transaction-weighted. #### Step 2: Join to users for the segment column `INNER JOIN users u ON u.user_id = ut.user_id`. Inner join is intentional: the statement keeps **users who never made a purchase** out of the comparison, so zero-spenders are out of the denominator. Pull `u.account_status` only; you do not need any other user column. #### Step 3: Aggregate up to `account_status` Outer `SELECT u.account_status, AVG(ut.total_spent) GROUP BY u.account_status`. Each user contributes exactly one number to the bucket regardless of whether they ran 1 transaction or 1,000. That is the user-weighted lifetime spend finance asked for. --- ### The solution **USER-WEIGHTED AVG VIA PRE-AGGREGATION** ```sql SELECT u.account_status, AVG(user_totals.total_spent) AS avg_total_spending FROM users u INNER JOIN ( SELECT user_id, SUM(total_amount) AS total_spent FROM transactions GROUP BY user_id ) user_totals ON u.user_id = user_totals.user_id GROUP BY u.account_status; ``` **Wrong, transaction-weighted** `SELECT u.account_status, AVG(t.total_amount) FROM users u JOIN transactions t ...` One power user with 1,000 purchases of $5 and one casual user with a single $5,000 purchase. Direct `AVG(total_amount)` = ($5,000 + $5,000) / 1,001 = **$9.99**. The power user's behavior dominates the bucket. **Right, user-weighted** Subquery collapses to two rows: $5,000 and $5,000. Outer `AVG` = $5,000. Both users count once. That is what the finance team means by average spend per user in an LTV model. > **The per-user rollup is the whole bill** > > 150M `transactions` hash-aggregated by `user_id` is the dominant cost: high-cardinality grouping over the full fact table, no partition prune available unless `transaction_date` is filtered. Expect a multi-GB hash table or a spill-to-disk sort-aggregate. The 8M-row `users` join is cheap by comparison, a hash join on the already-grouped build side keyed on a unique `user_id`. An index on `transactions(user_id)` does not help a full aggregate; hash-partitioning `transactions` by `user_id` would let workers aggregate slices in parallel. > **Asking average of what is the tell** > > Whether you ask **average of what?** before writing SQL. Per-user vs per-transaction is a business question, not a SQL question. Strong candidates name the trap out loud, propose the subquery shape, and confirm with the interviewer that zero-spenders are excluded. Weak candidates write `AVG(t.total_amount)` and move on, producing a number that is mathematically fine and semantically wrong. > **Transaction-weighted reads right and answers wrong** > > Writing `AVG(t.total_amount)` directly off the `users JOIN transactions` shape. It reads correctly but answers a different question: it weights by transaction count, so a status with one whale running thousands of small charges looks identical to a status of consistent big spenders. Producing the number without flagging the choice is the bug. > **Zero-Spender Decision** > > Swap to `LEFT JOIN` (users on the left, the subquery on the right) and wrap with `COALESCE(user_totals.total_spent, 0)` if finance wants zero-spenders included as $0. That shifts every bucket's average down, often dramatically for statuses like '`pending_verification`' or 'inactive' where many accounts never purchase. The statement keeps **non-buyers** out, so `INNER JOIN` is right here, but flag the alternative aloud: it is the first follow-up they will ask. --- ### COMMON FOLLOW-UP QUESTIONS ## Common follow-up questions - How would the answer change if finance wanted to include users with zero purchases as $0 spenders? _(Tests whether you can articulate `LEFT JOIN` + `COALESCE` and reason about the denominator shifting per `account_status`.)_ - Compute median spend per user by `account_status` instead of the mean. How? _(Probes window/aggregate functions: `PERCENTILE_CONT(0.5) WITHIN GROUP` over the pre-aggregated per-user totals, or `NTILE(2)` plus `MAX` on the lower half.)_ - What if `total_amount` can be negative for refunds, and finance wants net spend? _(Tests whether you defend `SUM` over filtering, and whether you would clip negatives, expose refund volume as its own column, or report both gross and net.)_ - How would you run this incrementally as transactions stream in, rather than scanning 150M rows nightly? _(Probes materialized per-user rollup tables, CDC into a `user_lifetime_spend` mart, and avoiding full re-aggregation on every refresh.)_ - How does the plan change if `transactions` is partitioned by `transaction_date` and finance only wants the trailing 90 days? _(Tests partition pruning awareness and whether the per-user `SUM` should sit inside a CTE with the date filter pushed in.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/average_spending_by_account_status) - [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.