Category Buyers

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

A merchandising analyst noticed the promotion shortlist keeps surfacing categories that fewer than three different customers have ever bought from. Using transactions and products, list the categories that clear that bar, with each one's number of unique buyers and total revenue, most revenue first.

Which categories have the broadest reach?

Tables

products

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

transactions

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

Expected output

Worked solution and explanation

A customer count dressed up as a sales report

Anyone can join transactions to products and sum total_amount per category. What separates candidates is counting the audience as different user_id values rather than purchase rows, then putting the at-least-three cutoff on that per-category count. On this data every category has about twenty purchases but only three buyers, so COUNT(*) reports roughly twenty everywhere, and 'Collectibles', which only two customers ever bought from, sails onto the shortlist it should miss.

Step 1: Attach a category to every sale

Inner-join transactions to products on product_id. A few sales carry a NULL product_id and cannot be placed in any category; the inner join drops them, which is right, because revenue you cannot attribute is not a category's revenue.

Step 2: Count people, not rows

COUNT(DISTINCT t.user_id) collapses a customer's repeat purchases into one buyer. Beside it, SUM(t.total_amount) adds every sale as stored, refunds and voids included, because revenue is the money that actually moved.

Step 3: Cut on the category's audience

The cutoff is a property of the category after its sales are rolled up, so it lives in HAVING COUNT(DISTINCT t.user_id) >= 3. A WHERE sees one sale at a time and has no buyer count to compare against.

Step 4: Lead with the money

ORDER BY total_revenue DESC sorts on the alias, so the revenue expression is written once and the biggest categories come first.

SELECT
    p.category,
    COUNT(DISTINCT t.user_id) AS unique_buyers,
    SUM(t.total_amount) AS total_revenue
FROM products p
JOIN transactions t ON p.product_id = t.product_id
GROUP BY p.category
HAVING COUNT(DISTINCT t.user_id) >= 3
ORDER BY total_revenue DESC

Counting purchases

COUNT(*) or COUNT(t.user_id) counts every sale row: about twenty per category, and 'Collectibles' with four sales clears a three-buyer cutoff it should fail.

Counting customers

COUNT(DISTINCT t.user_id) counts each customer once: three per category, and 'Collectibles' stops at two and drops out.

Common follow-up questions

• How would you keep categories under three buyers but show them at the bottom? (Moves the cutoff out of `HAVING` into a flag column or a `CASE` in the sort.)
• What if you also needed the average sale size per category? (Adds `AVG(t.total_amount)` alongside the other aggregates in the same rollup.)
• How does this behave with 10M products instead of 8K? (The product side no longer fits comfortably in memory, so the join turns into a partitioned shuffle join.)