Spending Range

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

L5

Problem

We're modeling spending variability for our regular customers, the ones with more than a dozen purchases in the transactions table, and we measure it as the gap between a customer's smallest and largest transaction amount taken as a percentage of that largest amount. List each regular's username from users alongside their smallest and largest amounts and that percentage rounded to one decimal place, most variable first.

Between the smallest purchase and the biggest lies the story.

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 is really testing

Strip away the spending-variability framing and this is a per-user cohort guard bolted onto a per-user min and max. Anyone can collapse transactions to a low and a high per user; what separates candidates is where the 'more than a dozen purchases' test lives and what it counts. It is a property of a user's whole transaction set, so it has to be evaluated per user, and it counts rows, repeats included. On this seed every buyer's history is six or seven purchases, each recorded twice, so a candidate who counts unique products tops out at seven and returns nothing at all, and one who drops the guard returns all fifteen buyers instead of the ten regulars.

Building it stage by stage

Step 1: Collapse each user to a low and a high

Inner-join users to transactions on user_id and group by the user, emitting MIN(total_amount) as low_amount and MAX(total_amount) as high_amount. One hash-aggregate pass over the join gives every user's range. The inner join drops users who never bought, which is correct: they have no range and could never clear the guard.

Step 2: Guard with a correlated count

In the outer query, keep a row only when (SELECT COUNT(*) FROM transactions AS t WHERE t.user_id = ur.user_id) exceeds twelve. Because it references ur.user_id from the grouped row, the count is scoped to exactly that user, and because it counts rows, a repeated purchase counts twice.

Step 3: Turn the gap into a share of the largest purchase

Divide high_amount - low_amount by high_amount, scale by 100.0 and round to one decimal as spread_pct. The raw gap cannot do this job on this seed: eight of the ten regulars share the same gap of 1212.3, while the share of the largest purchase separates every one of them. Order by spread_pct descending so the most variable customers read first.

The solution

WITH user_range AS (
  SELECT
    u.user_id,
    u.username,
    MIN(t.total_amount) AS low_amount,
    MAX(t.total_amount) AS high_amount
  FROM users AS u
  JOIN transactions AS t
    ON u.user_id = t.user_id
  GROUP BY
    u.user_id,
    u.username
)
SELECT
  username,
  low_amount,
  high_amount,
  ROUND(100.0 * (high_amount - low_amount) / high_amount, 1) AS spread_pct
FROM user_range AS ur
WHERE
  (
    SELECT
      COUNT(*)
    FROM transactions AS t
    WHERE
      t.user_id = ur.user_id
  ) > 12
ORDER BY
  spread_pct DESC

Correlated count in the outer filter

The guard reads as a self-contained per-user question and shows the correlated scalar subquery directly. It costs one indexed probe of transactions per candidate user.

Count folded into the grouped pass

Adding COUNT(*) to the user_range CTE and filtering with HAVING COUNT(*) > 12 rides along in the same grouped pass, with no second probe. It returns the same ten rows and is the form you ship at scale.

Common follow-up questions

• If a transaction could be recorded twice by mistake, how would you keep a duplicate from pushing a customer over the purchase cutoff? (Tests whether a row-level dedup before grouping changes both the count guard and the extremes.)
• How would you also return the date of each regular's largest transaction? (Tests reaching for a window function when per-row context must survive next to the aggregate.)
• What would you do if a customer's largest amount could be zero or negative? (Tests guarding the division with `NULLIF` or a `CASE` on the denominator.)