Across the Aisles

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

L4

Problem

Which of our shoppers spread their purchases under $500 across more than one product category? Working from our transactions and each product's category, list them with how many categories they bought from, most categories first.

The best customers never stay in one aisle.

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

products

Columns: product_id INTEGER, product_name TEXT, category TEXT, price REAL, rating REAL, in_stock INTEGER

Expected output

Worked solution and explanation

What this really tests

Under the marketing costume this is a per-shopper distinct count over a filtered slice, with a threshold on the count. Two decisions carry it. The $500 cut is a rule about each purchase, so it decides which rows exist before any shopper is summarized. The segment is about variety, so the number that matters is how many different categories survive the cut, not how many purchases. Count rows and a shopper whose only small buys are two orders from one aisle sails past 'more than one'. Drop the cut and every full history comes back, readmitting exactly the people the segment was built to exclude.

Building it

Step 1: Keep only purchases under $500

WHERE t.total_amount < 500 runs before grouping, which is right: the limit is a property of each purchase, not of the shopper. Once rows are grouped there is no single amount left to compare.

Step 2: Attach the category with an inner join

Join transactions to products on product_id. A purchase with a NULL product_id matches nothing and falls away: no product, no category.

Step 3: Count different categories per user

Group by t.user_id and compute COUNT(DISTINCT p.category). Repeat buys in one aisle collapse to one, so two small book orders still read as a single category.

Step 4: Threshold the count with HAVING

The count does not exist until rows are grouped, so HAVING COUNT(DISTINCT p.category) > 1 is the only place the rule can live. On this data it removes the shoppers whose small purchases all landed in one category.

Step 5: Sort widest reach first

ORDER BY category_count DESC. Shoppers tied on the count may come in any order, so no tiebreaker is owed.

The solution

SELECT t.user_id, COUNT(DISTINCT p.category) AS category_count
FROM transactions t
JOIN products p ON t.product_id = p.product_id
WHERE t.total_amount < 500
GROUP BY t.user_id
HAVING COUNT(DISTINCT p.category) > 1
ORDER BY category_count DESC

Counts purchases

HAVING COUNT(*) > 1 or COUNT(p.category) > 1 passes anyone with two small orders, even two from the same aisle, and reports purchase volume as category_count.

Counts categories

COUNT(DISTINCT p.category) in both the SELECT and the HAVING measures variety, so repeat buys count once and only real multi-category shoppers survive.

Common follow-up questions

• Restrict the segment to shoppers with at least three categories who also bought from 'Electronics' under $500. What changes? (Tests combining a distinct count with a conditional aggregate in `HAVING`.)
• What if the $500 limit applied to each shopper's total spend instead of each purchase? (Tests moving a condition from `WHERE` to `HAVING` and explaining why the answers differ.)
• How would you also list shoppers with no qualifying purchases, showing zero categories? (Tests an outer join from `users` and a count that handles `NULL`.)