# Before They Walk > A few names carry the whole ledger. Reach them first, before someone else does. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: mid ## Problem Our retention team is convinced that most of the revenue on this book of business comes from a tiny sliver of the customer base, and they want to stand up a white-glove loyalty program aimed at exactly those people before a competitor poaches them. They have asked us to pull the ten customers who have contributed the most money across every purchase they have ever made, and to put a human-readable name next to each one so the team can actually reach out, with the biggest spenders shown first. ## Worked solution and explanation ### Why this problem exists in real interviews This is the canonical revenue-concentration question, and it shows up on phone screens because it quietly checks four things at once: can you connect a fact table to a dimension table, can you collapse many rows per entity into one number, can you rank that number, and can you cut the list off at a useful size. The purchase facts live in transactions (one row per purchase, with total_amount and user_id), but the human identity lives in users (username keyed by user_id). A candidate has to recognize that total_amount must be summed per customer, then attach the name, then surface only the heaviest contributors. The decision that separates a clean answer from a messy one is grouping on user_id (the stable key) while still carrying username through for readability. --- ### Break down the requirements #### Step 1: Connect purchases to people Each transaction only carries a user_id, but the team needs a name. Match every transaction row to its owner in users on user_id so the username travels alongside the dollars. #### Step 2: Collapse to one total per customer A customer has many purchases, so add up total_amount per user_id. Group on the user_id (and carry username along) and SUM the amounts into a single lifetime_spend figure per person. #### Step 3: Rank and trim Sort the per-customer totals from largest to smallest and keep only the first ten rows, since the loyalty program targets just the heaviest spenders. --- ### The solution **Top ten by lifetime spend** ```sql SELECT u.user_id AS user_id, u.username AS username, SUM(t.total_amount) AS lifetime_spend FROM users u JOIN transactions t ON u.user_id = t.user_id GROUP BY u.user_id, u.username ORDER BY lifetime_spend DESC LIMIT 10; ``` > **Cost Analysis** > > On a production book this transactions table is the big one: picture roughly 80M purchase rows at about 12 GB, against a few million users. The aggregation is the dominant cost, so an index on transactions(user_id) lets the engine stream the join and partial-aggregate by key instead of building a full hash of the fact table. The ORDER BY ... LIMIT 10 is cheap because the engine keeps a bounded top-N heap rather than sorting all groups. If transactions is partitioned by transaction_date, this query still scans every partition since lifetime spend has no date bound, which is the honest tradeoff to call out. > **Interviewers Watch For** > > A strong candidate groups on user_id (the guaranteed-unique key) and explicitly carries username through the GROUP BY rather than wrapping it in a meaningless aggregate, and they say out loud that an inner join is correct here because a customer with zero purchases cannot be a top spender. They also name the column lifetime_spend instead of leaving it as the raw SUM expression. > **Common Pitfall** > > The classic mistake is grouping by username alone. If two different accounts share a display name their spend gets silently merged into one inflated row. Grouping by user_id (and including username for output) keeps each account distinct. The second trap is forgetting ORDER BY before LIMIT, which returns ten arbitrary customers rather than the ten biggest. --- ## Common follow-up questions - How would you also show each customer's number of separate purchases next to their total? _(Tests whether the candidate can add COUNT alongside SUM in the same grouped query without disturbing the ranking.)_ - If the team only cares about spend in the last 90 days, how does the query change and what happens to your partition scan? _(Tests date filtering in the WHERE clause and awareness that a bounded date range lets partition pruning kick in at scale.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/before_they_walk) - [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.