# Kings of the Aisle

> Every category crowns exactly one winner. Go find them.

Canonical URL: <https://datadriven.io/problems/kings-of-the-aisle>

Domain: SQL · Difficulty: medium · Seniority: mid

## Problem

The merchandising team is reviewing which product owned each category this year. For every category, surface the single product with the highest total sales, listed from the biggest earner down.

## Worked solution and explanation

### Why this problem exists in real interviews

This is a top-1-per-group problem wearing a merchandising costume, and it is the exact spot Visa-style SQL rounds probe your window-function instinct. Anyone can join transactions to products and sum revenue by category and product. The real question is how you collapse each category down to its single leader. Reach for MAX(total_revenue) in a second aggregation and you get the right number but lose the product_name attached to it, or you re-join and accidentally surface two products when a category has a tie. The clean move is to number the rows inside each category and keep number one.

---

### The schema you are reasoning about

---

### Break down the requirements

#### Step 1: Join to reach the category

category and product_name live on products, the revenue lives on transactions. Inner join on product_id so every sale carries its category label. An inner join is correct here: a transaction with no matching product cannot be attributed and should drop.

#### Step 2: Roll up to product-in-category revenue

Filter to this year with strftime('%Y', transaction_date) = '2026', then GROUP BY category, product_name and SUM(total_amount). This is the grain the leaderboard actually cares about, one row per product per category.

#### Step 3: Number within each category and keep the leader

ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_revenue DESC, product_name) stamps the top earner in each category as 1. Filtering rn = 1 gives exactly one product per category, and the product_name rides along untouched, which a bare MAX() would have severed.

---

### The solution

**Top product per category**

```sql
WITH product_revenue AS (
  SELECT p.category AS category,
         p.product_name AS product_name,
         SUM(t.total_amount) AS total_revenue
  FROM transactions t
  JOIN products p ON t.product_id = p.product_id
  WHERE strftime('%Y', t.transaction_date) = '2026'
  GROUP BY p.category, p.product_name
),
ranked AS (
  SELECT category,
         product_name,
         total_revenue,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_revenue DESC, product_name) AS rn
  FROM product_revenue
)
SELECT category, product_name, total_revenue
FROM ranked
WHERE rn = 1
ORDER BY total_revenue DESC
```

> **Why ROW_NUMBER, not RANK**
>
> ROW_NUMBER guarantees exactly one winner per category even when two products tie on revenue, because the secondary ORDER BY product_name breaks the tie deterministically. RANK would return both tied products and quietly inflate your category count. Pick ROW_NUMBER when the ask is literally 'the single leader'.

> **Cost Analysis**
>
> At 50M transactions and a few thousand products, the join hash-builds on the small products side and streams the fact table once. The year predicate on transaction_date prunes to a single partition before the aggregation, so the window function only sorts the already-collapsed per-product totals (thousands of rows), never the raw sales. Wrapping transaction_date in strftime defeats an index on that column, so in production compare against a literal date range instead.

> **Common Pitfall**
>
> Computing MAX(total_revenue) per category in a subquery and joining it back to recover the product_name. It works until two products in a category hit the same max, and then you get two rows for that category, silently double-counting your 'winners'. The window approach never has this failure mode.

---

## Common follow-up questions

- Now return the top three products per category instead of just the leader. _(Tests whether they see rn = 1 generalizes to rn <= 3 with no structural change.)_
- How would you handle categories that had zero sales this year but should still appear in the report? _(Pushes toward starting from products (or a category dimension) with a LEFT JOIN so empty categories survive.)_
- The team wants each product's share of its category's total revenue alongside the leader. _(Tests SUM() OVER (PARTITION BY category) as a second window in the same pass.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/kings-of-the-aisle)
- [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.