# The Ones Who Return > One purchase is a trial. Two is a habit. Find how many members formed one. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: mid ## 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. ## 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 **Above-average member percentage** ```sql 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; ``` > **Cost Analysis** > > On a production transactions table of roughly 240M rows spanning 18 months and about 30 GB, the per-member rollup is the expensive step: one hash aggregation on user_id, typically a few GB of spill if memory is tight, with no join to amplify the work. The scalar subquery for AVG(txn_count) reads the same derived member set once more; a planner that materializes the CTE computes it over tens of millions of member rows, cheap next to the initial scan. The outer conditional aggregation is another pass over that member set with no sort, so the whole thing stays a linear scan plus a couple of grouping passes. > **Interviewers Watch For** > > A strong candidate says out loud that the threshold is the average of per-member counts, not an average over rows, and derives it from the same rolled-up set rather than hardcoding a number. They compute the share over members, not rows, and pre-empt integer division by multiplying by 100.0 before dividing. Bonus points for noting the denominator excludes members with zero transactions because such members never appear in this table at all. > **Common Pitfall** > > The classic mistake is computing everything at the row grain: comparing each raw transaction to the average and dividing repeat-buyer rows by total rows, which massively overstates the result because heavy buyers contribute many rows and drag the average with them. The second most common bug is averaging purchases per transaction row instead of per member, which is a different quantity entirely. The third is integer division: writing 100 * SUM(...) / COUNT(*) without a float, which truncates to 0 in SQLite and most engines when the ratio is below 1. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_ones_who_return) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.