Top Buyers of Premium Products

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

We run an online marketplace and want to see which categories draw the biggest crowd of premium shoppers, treating a product as premium once its rating reaches 4.0. For each category, count how many different shoppers bought one of its premium products, then give the categories a standing by that crowd, the biggest first.

Which users bought the most top-rated products

Tables

products

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

order_items

Columns: item_id INTEGER, order_id INTEGER, product_id INTEGER, user_id INTEGER, quantity INTEGER, unit_price REAL

Expected output

Worked solution and explanation

What this problem is really testing

This is a per-category distinct-buyer count wearing a merchandising costume. The real skill: filter products to the premium set, join to order_items, and count the distinct people behind the purchases, not the rows. Every buyer here sits on two line items per product, so COUNT(*) reports twice the true audience and every category looks artificially large. Then you have to position the categories so equals share a place, which is where DENSE_RANK separates from RANK and ROW_NUMBER.

Walk through it

Step 1: Isolate the premium products

In a premium_products CTE, keep only rows where rating >= 4.0. That comparison also drops the NULL-rated products for free (a NULL rating is never >= 4.0), so the join downstream only ever sees genuinely premium goods.

Step 2: Join to purchases and count real buyers

INNER JOIN order_items on product_id, group by category, and take COUNT(DISTINCT oi.user_id). The DISTINCT is the whole game: each buyer appears on multiple line items per product, so COUNT(*) would report the crowd at double size.

Step 3: Award a shared standing

Over that per-category buyer_count, apply DENSE_RANK() OVER (ORDER BY buyer_count DESC). Categories with the same crowd get the same rank_pos, and the next tier down is the very next number, no gap.

Step 4: Order the leaderboard

Finish with ORDER BY buyer_count DESC, category. The category tiebreak makes the order total, so two runs never disagree on how equals are laid out.

The solution

WITH premium_products AS (
  SELECT product_id, category
  FROM products
  WHERE rating >= 4.0
),
category_buyers AS (
  SELECT
    pp.category,
    COUNT(DISTINCT oi.user_id) AS buyer_count
  FROM premium_products pp
  INNER JOIN order_items oi
    ON oi.product_id = pp.product_id
  GROUP BY pp.category
)
SELECT
  category,
  buyer_count,
  DENSE_RANK() OVER (ORDER BY buyer_count DESC) AS rank_pos
FROM category_buyers
ORDER BY buyer_count DESC, category

Common follow-up questions

Common follow-up questions

• Count distinct premium products bought per category instead of distinct buyers. (Swap `COUNT(DISTINCT oi.user_id)` for `COUNT(DISTINCT oi.product_id)` and reason about how the join grain changes the answer.)
• Only count purchases from the last 12 months. (Forces an `order_items` date predicate and a conversation about index coverage on the big table.)
• Why is `INNER JOIN` right here rather than `LEFT JOIN`? (Checks that a category with no premium purchases is absent by design, not silently zero.)