The Ones Who Return

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

mid

Asked in

CVS Health

Problem

Our member growth team is prepping the quarterly retention review and wants a single number for how top-heavy the buying really is: the members who shop more often than the average customer are the ones carrying the business. Counting every purchase as one order, give the share of the member base that makes more purchases than the average member, as a percentage rounded to two decimals.

One purchase is a trial. Two is a habit. Find how many members formed one.

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

What this problem is really testing

Strip the business framing and this is a share-above-a-derived-threshold question, and the threshold is the sneaky part: it is the average of a number that does not exist in the raw table. The transactions table has one row per purchase, but 'the average member' is defined over per-member purchase counts, so you have to roll the table up to one count per member, take the average of THOSE counts, and only then ask how many members sit above it. Reach for a fixed cutoff or compute the average over raw rows and you are answering a different question. The trap that sinks people is comparing at the wrong grain: average purchases-per-row is not average purchases-per-member, and thresholding rows instead of members counts your heaviest buyers many times over, which corrupts both the cutoff and the population.

Break down the requirements

Step 1: Collapse purchases to members

Each member can have many rows in transactions. Roll the table up by user_id and count rows per member so you have one number, txn_count, describing how active each member is. This per-member summary is the population you actually reason about, and it is also the set you will draw the threshold from.

Step 2: Derive the average from the members, not the rows

The cutoff is not a literal you can type in. It is the average of txn_count across the members you just rolled up, so compute it with a scalar subquery over the same summary: SELECT AVG(txn_count) FROM member_activity. Computing the average over raw transaction rows instead would give a different, wrong number.

Step 3: Flag members above the bar and divide by the full base

A member clears the bar when their txn_count exceeds that average. Use a conditional expression that yields 1 for those members and 0 otherwise, sum it to count them, then divide by the total number of members in the summary. Multiply by 100.0 to force floating-point math and round to two decimals. The denominator is the count of members, not the count of transaction rows.

The solution

WITH member_activity AS (
  SELECT user_id, COUNT(*) AS txn_count
  FROM transactions
  GROUP BY user_id
)
SELECT ROUND(100.0 * SUM(CASE WHEN txn_count > (SELECT AVG(txn_count) FROM member_activity) THEN 1 ELSE 0 END) / COUNT(*), 2) AS above_avg_member_pct
FROM member_activity;

Common follow-up questions

• How would you report the above-average share per month of signup rather than a single all-time number, with each cohort measured against its own average? (Tests joining to a member dimension for the signup date and grouping the outer aggregation by a cohort key while recomputing the per-cohort average without double-counting members.)
• If the table contained accidental duplicate purchase rows, how would your number move and how would you guard against it? (Tests awareness that duplicate rows inflate per-member counts, push members above the average, and shift the average itself, and whether the candidate would deduplicate on a natural key first.)