Top Per Category

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

hard

Seniority

L4

Problem

Editorial is crowning the best-rated product in each category, and every product that matches that top rating gets featured. Drop products that have no rating yet, and return the product_name, category, and rating of each winner.

Every category has a champion. Crown them all.

Tables

products

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

Expected output

Worked solution and explanation

What this really is

This is a per-category maximum wearing a merchandising costume, and the real test is tie inclusion. Anyone can find the top rating in each category. The trick is returning every product that reaches it, not one, while keeping unrated products out of the picture. Express it as an anti-join: keep each product for which no product in the same category scores higher. Reach for a top-1-per-group shortcut instead and you crown a single champion while the co-leaders vanish, and if you leave the unrated rows in, a NULL rating poses as a leader because nothing beats a NULL.

Walk the logic

Step 1: Drop the unrated products

Guard the outer row with p.rating IS NOT NULL. This is the guard that actually bites: an unrated product has no same-category peer that scores higher, so without this filter it would survive the anti-join and look like a category leader with no score at all.

Step 2: Keep the products nobody beats

For each surviving product, ask whether any product in the SAME category has a strictly higher rating. NOT EXISTS (SELECT 1 FROM products q WHERE q.category = p.category AND q.rating > p.rating) keeps only the products nobody in their category beats. That set IS the per-category maximum.

Step 3: Ties survive automatically

Ties fall out for free. Two products sharing a category's top rating each have no strictly-higher peer, so both satisfy NOT EXISTS and both appear. No extra clause, no window function, no post-processing.

The solution

SELECT p.product_name,
       p.category,
       p.rating
FROM products AS p
WHERE p.rating IS NOT NULL
  AND NOT EXISTS (
        SELECT 1
        FROM products AS q
        WHERE q.category = p.category
          AND q.rating > p.rating
  )

Common follow-up questions

• Rewrite this with a window function. Which ranking function keeps the tied winners, and why? (Tests whether the candidate can translate the anti-join into a window function and knows that `DENSE_RANK` (not `ROW_NUMBER`) preserves ties.)
• If a category has three products all at rating 4.6, how many rows come back for that category? Trace it. (Tests tie semantics; both tied products have no strictly-higher peer, so both satisfy NOT EXISTS.)
• At 20K products across 22 categories, what is the cost of this correlated NOT EXISTS, and which index makes it cheap? (Tests scaling awareness of the correlated subquery and the supporting index.)