# Top Per Category

> Every category has a champion. Crown them all.

Canonical URL: <https://datadriven.io/problems/top_per_category>

Domain: SQL · Difficulty: hard · Seniority: L4

## Problem

Editorial wants to feature the top-rated product in every category. Within each category, find the product(s) with the highest rating and include all ties. Skip products with no rating. Return the product_name, category, and rating.

## Worked solution and explanation

### Why this problem exists in real interviews

Working with `products`, this problem tests per-group ranking via `ROW_NUMBER()` or `DENSE_RANK()` partitioned by a grouping key. The interviewer checks whether you choose the right window function and aggregate to the correct grain before ranking.

---

### Break down the requirements

#### Step 1: Aggregate per category

`GROUP BY category` with the appropriate aggregate function produces one summary row per group from the `products` table.

#### Step 2: Rank within each category

Use `ROW_NUMBER() OVER (PARTITION BY category ORDER BY aggregate DESC)` to rank entries within each partition.

#### Step 3: Filter to top entries

Wrap in a subquery and filter `WHERE rn <= N` to keep only the top entries per group.

---

### The solution

**Exclude null ratings, dense-rank products by rating within each category**

```sql
SELECT category, category, total_price
FROM (
    SELECT
        category,
        category,
        SUM(price) AS total_price,
        ROW_NUMBER() OVER (
            PARTITION BY category
            ORDER BY SUM(price) DESC
        ) AS rn
    FROM products
    GROUP BY category, category
) ranked
WHERE rn <= 10
ORDER BY category, total_price DESC
```

> **Cost Analysis**
>
> The GROUP BY reduces the 20K-row `products` table to the number of distinct `category` values. The window function sorts within each partition. A covering index on `(category, price)` enables an index-only aggregate scan.

> **Interviewers Watch For**
>
> Interviewers specifically test whether you use `PARTITION BY` in the window function. Omitting it gives a global ranking instead of per-group, which is at its core different.

> **Common Pitfall**
>
> Using `ORDER BY ... LIMIT` instead of a window function for per-group ranking. LIMIT gives N rows globally, not per group. Per-group top-N always requires a window function.

---

## Common follow-up questions

- If you forget to exclude NULL ratings, where do they appear in the DENSE_RANK ordering? _(Tests NULL sort behavior; NULLs sort last by default in PostgreSQL, potentially getting a low rank instead of being excluded.)_
- If a category has all products with the same rating, does every product in that category appear? _(Tests DENSE_RANK behavior; all products get rank 1, and the rank = 1 filter returns them all.)_
- Would a correlated subquery (WHERE rating = (SELECT MAX(rating) ... WHERE category = outer.category)) be simpler than DENSE_RANK? _(Tests alternative approaches; the subquery is simpler but may be less performant with many categories.)_

## Related

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