# Best in Class

> Every category crowns exactly one earner. Find who wears it.

Canonical URL: <https://datadriven.io/problems/best-in-class-category-top-earner>

Domain: SQL · Difficulty: medium · Seniority: mid

## Problem

We run an online marketplace where every category has one clear top earner. For each product category, find the product that brought in the most revenue, listed from the highest earner down.

## Worked solution and explanation

### What this problem really is

Strip the merchandising costume and this is a top-1-per-group, an arg max within each category. Anyone can total revenue per product; the separator is returning the PRODUCT that owns the max, not the max number. The tempting move, GROUP BY category with MAX(revenue), hands you the winning amount but loses the product_name attached to it. Reach for MAX and you can report a dollar figure you cannot name.

---

### Break down the requirements

#### Step 1: Get revenue to the product grain

Revenue lives in transactions, category lives in products, so join on product_id and SUM(total_amount) grouped by category and product_name. This collapses many transactions into one revenue figure per product, which is the unit the ranking will compare.

#### Step 2: Rank inside each category, not globally

PARTITION BY category restarts the ordering per category so a strong Electronics product never crowds out the top Books product. ORDER BY product_revenue DESC puts the earner first. RANK (not ROW_NUMBER) is deliberate: if two products tie for the top of a category, both should be crowned, and RANK gives them both position 1.

#### Step 3: Keep the winners, sort the board

Filter to category_rank = 1 to keep one winner per category (or several on a tie), then ORDER BY product_revenue DESC so the leaderboard reads biggest earner down.

---

### The solution

**Top earner per category**

```sql
WITH product_revenue AS (
  SELECT p.category AS category,
         p.product_name AS product_name,
         SUM(t.total_amount) AS product_revenue
  FROM transactions t
  JOIN products p ON t.product_id = p.product_id
  GROUP BY p.category, p.product_name
),
ranked AS (
  SELECT category,
         product_name,
         product_revenue,
         RANK() OVER (PARTITION BY category ORDER BY product_revenue DESC) AS category_rank
  FROM product_revenue
)
SELECT category,
       product_name,
       product_revenue
FROM ranked
WHERE category_rank = 1
ORDER BY product_revenue DESC
```

**MAX in a plain GROUP BY**

SELECT category, MAX(revenue) FROM ... GROUP BY category returns the winning amount but strips product_name. Re-joining that max back to find the name reintroduces the tie problem and an extra pass.

**Window rank**

One aggregation, one windowed pass, then a filter. The product_name rides along with its revenue the whole way, and ties fall out naturally from RANK.

> **Common Pitfall**
>
> Using ROW_NUMBER instead of RANK. On a genuine tie for the category lead, ROW_NUMBER arbitrarily picks one product and silently drops the other, so a real co-leader vanishes from the board. If the business says 'the top product,' confirm whether co-leaders should both show before choosing.

> **Interviewers Watch For**
>
> Whether you aggregate to the product grain BEFORE ranking. Candidates who rank raw transaction rows compare individual purchases, not product totals, and crown whoever had one large order. State the grain out loud: total per product, then rank within category.

> **Cost Analysis**
>
> At 400M transactions and a few hundred thousand products, the join and SUM hash-aggregate in one pass to a small per-product set; the window then sorts only those aggregated rows, which is cheap. Partition pruning on transaction_date helps only if you add a date filter. The plan never materializes the full transaction table twice, unlike the MAX-then-rejoin shape.

---

## Common follow-up questions

- Now give me the top three products per category instead of just the leader. _(Tests whether they change the filter to category_rank <= 3 and understand DENSE_RANK versus RANK for gapped positions.)_
- What if 'best-selling' means units sold rather than revenue? _(Probes whether they can swap SUM(total_amount) for SUM(quantity) without disturbing the ranking structure.)_
- How would you restrict this to the most recent year without risking empty categories? _(Pushes toward a transaction_date filter pushed into the base scan and reasoning about categories that had no sales in the window.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/best-in-class-category-top-earner)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.