Top Shelf

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

Domain

SQL

Difficulty

easy

Seniority

L3

Problem

Each product category is judged by its weakest link, the lowest-rated item a shopper might land on, where items no one has rated yet don't count and a tie for that lowest rating goes to the priciest item. Give the catalog quality team the three categories whose weakest item still scores highest, strongest first.

Buyers need to know ceiling prices before negotiating with vendors.

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 arg-min wearing a quality-review costume: for each category you need the single lowest-rated row and you have to carry product_name out with it. The floor number is the easy half, any grouping hands you MIN(rating). The real question is naming the product that sits on that floor. The moment you collapse a category with GROUP BY and MIN(rating), product_name is gone, and bolting it onto the grouped query leans on a non-portable engine quirk that returns some arbitrary row's name. There are two traps underneath. First, an unrated product (rating IS NULL) is not a rating of zero, and under an ascending sort NULL slides to the very front, so if you forget to drop those rows a phantom floor hijacks the answer. Second, when several products share a category's lowest rating you have to pin exactly one, or the product_name cell is whatever the engine felt like returning.

Break down the requirements

Step 1: Drop the unrated items first

Filter to WHERE rating IS NOT NULL before anything else. A shopper judges a product by its rating, so an item nobody has rated cannot be a category's weak link. This is load-bearing, not defensive: because SQLite sorts NULL first under ORDER BY rating ASC, an unrated row would otherwise win rn = 1 and post a floor of NULL.

Step 2: Rank within each category, weakest first

Number the rows inside each category with ROW_NUMBER() OVER (PARTITION BY category ORDER BY rating ASC, price DESC), then keep rn = 1. rating ASC puts the weakest product first; price DESC names the priciest when several share that lowest rating; rn = 1 is what keeps product_name glued to its own floor instead of losing it to an aggregate.

Step 3: Take the three highest floors

Each category's rn = 1 row carries its floor in floor_rating. Order those rows by floor_rating DESC and LIMIT 3 to hand back the three categories whose weakest product still scores highest. Two categories can share a floor and their relative order does not matter; what would matter is a cut after the third row that splits a tie, and on this data it does not.

The solution

SELECT category, product_name, floor_rating
FROM (
    SELECT
        category,
        product_name,
        rating AS floor_rating,
        ROW_NUMBER() OVER (
            PARTITION BY category
            ORDER BY rating ASC, price DESC
        ) AS rn
    FROM products
    WHERE rating IS NOT NULL
) ranked
WHERE rn = 1
ORDER BY floor_rating DESC LIMIT 3;

Common follow-up questions

• What if the team wanted each category's best product instead of its weakest? (Tests whether they flip to `ORDER BY rating DESC` and notice the `NULL`-handling reverses: unrated rows now sort last, so the `IS NOT NULL` filter stops being load-bearing.)
• How would you return every product tied at a category's lowest rating, not just one? (Tests `RANK()` over `ROW_NUMBER()`: `RANK` surfaces every product tied at the floor rather than collapsing to one.)
• What if an unrated product should count as the lowest possible rating instead of being ignored? (Tests `COALESCE(rating, 0)` in the sort instead of the `WHERE` filter, and whether they see it changes which categories win.)