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

L4

Asked in

Capital One

Problem

We're segmenting the platform's users by account status to see how revenue splits across them, and each status should count all of its users, even the ones who have never made a transaction. For each status with more than 50 transactions, show the transaction count, the number of unique users, and the total revenue, from highest revenue down.

Segment user spending and activity by account status across the platform

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

What this problem is really testing

Strip the business dressing and this is a grouped aggregate over a base you must not shrink, gated by a threshold that actually has to cut something. Anyone can group by account status and add up the money. Two moves separate the strong answers. First, the user tally has to count everyone in a status, buyers or not, so the join has to be a LEFT JOIN from users; reach for an inner join and every user who never transacted vanishes, quietly turning user_count into a buyer count. Second, the more-than-50-transactions cutoff is a filter on a per-group count, so it belongs in HAVING, not WHERE, and here it genuinely drops the 40-transaction 'active' status. Skip it and that status rides along in a report it does not qualify for.

Building the query

Step 1: Join from users, and keep the ones who never bought

Start from users and LEFT JOIN transactions on user_id. This keeps one row per user even when they have no matching transaction, so a status full of dormant accounts still contributes its people to the user count. An inner join would drop those users before you ever counted them.

Step 2: Aggregate each metric per status

GROUP BY u.account_status collapses to one row per status. COUNT(t.transaction_id) counts real transactions (NULLs from non-transacting users are ignored), COUNT(DISTINCT u.user_id) counts unique people so a heavy buyer with many transaction rows is still one user, and SUM(t.total_amount) totals the revenue.

Step 3: Filter on the aggregate with HAVING

HAVING COUNT(t.transaction_id) > 50 runs after the grouping, so it can test the per-group transaction count. WHERE cannot: it filters raw rows before the count exists. On this data that single line is what removes the 'active' status, which lands at 40 transactions.

Step 4: Order by revenue, highest first

ORDER BY total_revenue DESC puts the biggest earners first. pending_verification and 'suspended' both sit at 54 transactions, and that is harmless: the order is by revenue, not by count, and the three revenues differ.

The solution

SELECT u.account_status,
       COUNT(t.transaction_id) AS transaction_count,
       COUNT(DISTINCT u.user_id) AS user_count,
       SUM(t.total_amount) AS total_revenue
FROM users u
LEFT JOIN transactions t ON u.user_id = t.user_id
GROUP BY u.account_status
HAVING COUNT(t.transaction_id) > 50
ORDER BY total_revenue DESC

Inner join

user_count = distinct buyers only; dormant accounts disappear from the tally.

LEFT join

user_count = every user in the status; the transaction count and revenue still come only from real transactions.

Common follow-up questions

• Suppose we only care about statuses whose average transaction value clears a bar, not their transaction count. How does the HAVING clause change? (Tests whether the candidate can move a post-aggregation threshold onto a derived metric rather than a raw count.)
• If a status had zero transactions, what would this query return for it, and at which clause does it get removed from the output? (Tests understanding of how the LEFT JOIN and the HAVING interact for empty groups.)
• `account_status` is heavily skewed toward a couple of values. How would that skew affect a parallel GROUP BY, and what would you do about the hot partition? (Tests reasoning about skew in `account_status` on distributed execution.)