# Diverse Shoppers > They shop the whole catalog. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem The retention team is building a loyalty tier for shoppers who range widely across the catalog rather than repeat-buying one item. For each customer who has purchased at least six different products, show their username, how many different products they have bought, and their total spend across all transactions. ## Tables ### users - user_id INTEGER - username TEXT - email TEXT - signup_date TEXT - account_status TEXT - age_bucket TEXT Sample rows (10 of 200): | user_id | username | email | signup_date | account_status | age_bucket | | --- | --- | --- | --- | --- | --- | | 100 | alice | alice@example.com | 2025-02-02 | inactive | 25-34 | | 197 | aaron42 | aaron42@example.com | 2026-03-03 | suspended | 35-44 | | 294 | amelia | amelia@example.com | 2024-04-04 | pending_verification | 45-54 | | 391 | arjun | arjun@example.com | 2025-05-05 | active | 55-64 | | 488 | ava99 | ava99@example.com | 2026-06-06 | inactive | 65+ | | 585 | andrew_k | andrew_k@example.com | 2024-07-07 | suspended | NULL | | 682 | anika | anika@example.com | 2025-08-08 16:34:00 | pending_verification | 18-24 | | 779 | aiden | aiden@example.com | 2026-09-09 14:53:00 | active | 25-34 | | 876 | aria_b | aria_b@example.com | 2024-10-10 | inactive | 35-44 | | 973 | brian | brian@example.com | 2025-11-11 | suspended | 45-54 | ### transactions - transaction_id INTEGER - user_id INTEGER - product_id INTEGER - quantity INTEGER - total_amount REAL - transaction_date TEXT Sample rows (10 of 200): | transaction_id | user_id | product_id | quantity | total_amount | transaction_date | | --- | --- | --- | --- | --- | --- | | 1067 | 197 | 1001 | 2 | 23.46 | 2026-02-02 | | 1134 | 294 | 1050 | 3 | 36.93 | 2026-03-03 | | 1201 | 391 | 1099 | 4 | 50.4 | 2026-04-04 | | 1268 | 488 | 1148 | 5 | 63.87 | 2026-05-05 | | 1335 | 585 | 1197 | 0 | 0 | 2026-06-06 | | 1402 | 682 | 1246 | 2 | 90.81 | 2026-07-07 | | 1469 | 779 | 1295 | 3 | 104.28 | 2026-08-08 | | 1536 | 876 | 1344 | 4 | 117.75 | 2026-09-09 | | 1603 | 973 | 1393 | 5 | -45 | 2026-01-10 | | 1670 | 1070 | 1442 | 1 | 144.69 | 2026-02-11 | ## Expected output First 5 of 12 rows: | username | distinct_products | total_spend | | --- | --- | --- | | alice | 6 | 8605.98 | | aaron42 | 7 | 8814.54 | | amelia | 6 | 9003.12 | | arjun | 6 | 9191.7 | | ava99 | 7 | 9380.28 | ## Worked solution and explanation ### What this problem really is Strip the loyalty-tier framing and this is a product-variety count with a cutoff you can only apply after aggregating. The real question: can you count DISTINCT products per buyer, not transactions, and then filter the groups on that count? A plain WHERE cannot touch a per-group aggregate, and COUNT(*) counts repeat purchases, so the shopper who bought one hoodie eight times looks identical to one who tried eight different items. Get either wrong and your loyalty tier fills with single-item repeat buyers, the exact customers you meant to exclude. --- ### Break down the requirements #### Step 1: Join users to transactions Join on `user_id` so each transaction carries its buyer's `username`. An inner join is what you want here: it drops users who never bought anything, which is correct because the tier is about buyers. #### Step 2: Aggregate per user `COUNT(DISTINCT product_id)` gives product variety (re-buying the same item does not inflate it), and `SUM(total_amount)` gives total spend. Both are computed per user with `GROUP BY u.user_id, u.username`. #### Step 3: Filter and sort `HAVING COUNT(DISTINCT product_id) >= 6` keeps only the broad shoppers. This has to be HAVING, not WHERE: the count only exists after the rows are grouped. Then `ORDER BY total_spend DESC, u.username` for a stable highest-first order. --- ### The solution **Multi-metric aggregation with a variety cutoff** ```sql SELECT u.username, COUNT(DISTINCT t.product_id) AS distinct_products, SUM(t.total_amount) AS total_spend FROM users u JOIN transactions t ON u.user_id = t.user_id GROUP BY u.user_id, u.username HAVING COUNT(DISTINCT t.product_id) >= 6 ``` > **Cost Analysis** > > Hash join of 5M users to 50M transactions, then a grouped aggregation. COUNT(DISTINCT `product_id`) keeps a hash set of product ids per group; across millions of active buyers that memory adds up, so the engine spills to a sort-based distinct when it has to. > **Interviewers Watch For** > > Whether the candidate writes COUNT(DISTINCT `product_id`) (correct) or COUNT(`product_id`)/COUNT(*) (wrong: counts total purchases, so a repeat-buyer of one item clears the six-product cutoff). > **Common Pitfall** > > Grouping by username alone can merge two different users who happen to share a name, and it silently changes the counts. Always include the primary key in GROUP BY when you select other columns alongside the aggregates. --- ## Common follow-up questions - How would you also show each user's single most frequently purchased product? _(Tests mode-per-group with a window function.)_ - What if you needed the average spend per unique product? _(Divide `total_spend` by `distinct_products`.)_ - How would you find users who are both broad buyers AND high-spending? _(Add a HAVING condition on SUM(`total_amount`) as well.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/diverse_shoppers) - [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.