# Across the Aisles > The best customers never stay in one aisle. Canonical URL: Domain: SQL · Difficulty: hard · Seniority: L4 ## Problem The recommendations team is building a cross-category shopper segment. Find the users whose purchases span more than one product category, ignoring any purchase whose product has no category on file. Return each qualifying user with the number of categories they've bought from, most categories first. ## Worked solution and explanation ### What this really tests Strip off the marketing costume and this is a per-user distinct-count with a threshold. The whole problem lives in one decision: what are you counting? The segment is defined by variety, so the metric is the number of DISTINCT categories a user has touched, not the number of purchases and not the number of products. The trap is counting the wrong thing. A user who bought five different books has one category, not five. Count purchases or products and every heavy buyer floods your 'cross-category' segment, which is exactly the audience you were trying to exclude. > **Trick to solving** > > The category name lives on products, not on transactions. So the real shape is: bring each transaction its product's category, then per user count how many DISTINCT categories showed up, then keep only users where that count exceeds one. Everything else is plumbing. --- ### Building it #### Step 1: Attach the category to each purchase Join `transactions` to `products` on `product_id` so each purchase carries its category. An inner join is deliberate here: purchases whose product is missing (or whose product has no category) should not count toward variety, and the join plus the DISTINCT count quietly drop them. #### Step 2: Count distinct categories per user Group by `user_id` and compute `COUNT(DISTINCT p.category)`. The DISTINCT is the entire point: it collapses repeat purchases in the same aisle down to one, so ten book orders still read as a single category. It also skips NULL categories for free, which is why no-category purchases never inflate the count. #### Step 3: Filter on the aggregate with HAVING You cannot put this condition in WHERE, because the thing you are filtering on does not exist until after the rows are grouped. `HAVING COUNT(DISTINCT p.category) > 1` runs after aggregation and keeps only the users who actually spanned more than one aisle. #### Step 4: Order by breadth, widest first Order by `category_count` descending so the widest-ranging shoppers surface first. If exact tie-breaking matters downstream, add `user_id` as a secondary key to make the output deterministic. --- ### The solution **Distinct categories per user with a HAVING threshold** ```sql SELECT t.user_id, COUNT(DISTINCT p.category) AS category_count FROM transactions t JOIN products p ON t.product_id = p.product_id GROUP BY t.user_id HAVING COUNT(DISTINCT p.category) > 1 ORDER BY category_count DESC ``` > **Common pitfall** > > The classic wrong answer is COUNT(*) or COUNT(DISTINCT p.product_id). Both reward volume, not variety: a loyal single-aisle buyer with many orders sails past the threshold. Only COUNT(DISTINCT p.category) measures the thing the segment is named after. > **Interviewers watch for** > > The tell of a strong candidate is naming the grain out loud before writing SQL: 'one row per user, counting distinct categories.' Interviewers also listen for whether you can defend WHERE versus HAVING, and whether you noticed that the inner join plus DISTINCT already handle missing and null categories without an extra clause. > **Cost at scale** > > At scale this reads 120M transactions joined to a 40K-row products dimension. Products is small enough to sit in a hash table on one side, so the join is a single streaming pass. The DISTINCT-within-group aggregation is the real cost; category has only about 25 values, so the per-user distinct set stays tiny and memory stays flat even across millions of users. --- ## Common follow-up questions - Now restrict the segment to users who bought from at least three different categories AND from the Electronics category specifically. How does the query change? _(Tests whether the candidate can extend a per-group distinct count into a targeted filter.)_ - How would you make the 'ignore products with no category' rule explicit rather than relying on DISTINCT skipping NULLs? _(Tests understanding of why COUNT(DISTINCT category) already excludes them, versus adding an explicit predicate.)_ - With 120M transactions, what would you index or pre-aggregate to keep this fast if it ran hourly? _(Tests scaling intuition on the aggregation, not just the join.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/across_the_aisles) - [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.