Above the Curve

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

Problem

The growth team is building a high-value customer segment for an upcoming campaign. Compute each user's total spend, then surface only the users who land above the average customer's spend, showing their ID, total, and how far above that benchmark they sit. List the biggest spenders first.

Spenders who break from the pack.

Tables

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

This is a grain-mismatch trap wearing a marketing costume. Anyone can group and sum; what separates candidates is where they measure the average. Compute AVG(total_amount) over transaction rows and you get the size of a typical purchase, a number far below what a typical customer spends over all their purchases, so nearly everyone clears the bar and the segment stops being high-value. The average has to be taken over the per-user totals, and HAVING alone cannot reach that overall number, which is exactly why the CTE plus scalar subquery is load-bearing here.

Break down the requirements

Step 1: Roll up to user grain

Aggregate transactions with GROUP BY user_id and SUM(total_amount). This is the unit the prompt cares about, not raw rows.

Step 2: Define the threshold

The cutoff is AVG(total) over the per-user totals, not AVG(total_amount) over transactions. Put the rollup in a CTE so the average runs against the right grain.

Step 3: Filter and project the delta

Keep users where total > (SELECT AVG(total) FROM totals), then project total - that_average as above_avg. The scalar subquery runs once.

Step 4: Sort biggest first

ORDER BY total DESC so the campaign team sees the whales at the top.

The solution

WITH totals AS (
  SELECT user_id, SUM(total_amount) AS total
  FROM transactions
  GROUP BY user_id
)
SELECT
  user_id,
  total,
  total - (SELECT AVG(total) FROM totals) AS above_avg
FROM totals
WHERE total > (SELECT AVG(total) FROM totals)
ORDER BY total DESC

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• How would you rewrite this using a window function instead of a scalar subquery? (Probes whether you can swap `(SELECT AVG(total) FROM totals)` for `AVG(total) OVER ()` and reason about the tradeoff.)
• What changes if we want users above the 90th percentile instead of the mean? (Tests `PERCENTILE_CONT` or `NTILE(10)` and whether you understand mean versus quantile thresholds.)
• How do you make this incremental over a rolling 30 day window? (Pushes you on `WHERE transaction_date >= CURRENT_DATE - INTERVAL '30 day'` and partition pruning on the 50M row table.)
• What if `total_amount` can be negative due to refunds? (Surfaces whether you net them, exclude them, or treat refunds as a separate signal.)