Average Spending by Account Status

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

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.

Average per-user lifetime spending segmented by account status

Tables

users

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

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

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

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.

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.)