Priciest Item in Each Category

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

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

Amazon

Problem

When two products share a category's top price, neither is more expensive than the other, so the merchandiser building the 'priciest in the aisle' strip wants every product at that price shown. From the products table, list each category's priciest products with their price, categories alphabetically and the products within a category alphabetically by name.

The most expensive item per category.

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-group top-1 dressed up as a merchandising strip, and what separates candidates is what happens at the top of each aisle. Anyone can find the highest price. The skill is returning the product that carries it, once per category, without quietly choosing one product when two share that price. Compute the top over the whole catalog and you get one row, Electronics at 1859.72, instead of eleven. Number the rows per category and keep the first, and your answer matches today's catalog by luck, then drops a tied product the first day two items share a category's top price.

Step 1: Scope the top price to each category

The question is per aisle, so the comparison has to restart in every category. PARTITION BY category does that: Collectibles, with its two products, is compared only against itself. That is why its winner is 'Ultra Tool 14X' at 115.41 while every other aisle tops out above 1,700.

Step 2: Give tied prices the same position

DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) gives position 1 to every product at a category's top price. That is the whole tie rule in one function: the ranking keeps the promise in the statement, not a later cleanup.

Step 3: Keep position 1, then sort for the shopper

Filter rnk = 1 in the outer query, because a window result cannot be filtered in the same SELECT that computes it. Then sort by category and product_name, the order the page shows. Both keys are output columns, so a tied pair lands alphabetically with no extra tiebreaker.

SELECT category, product_name, price
FROM (
    SELECT
        category,
        product_name,
        price,
        DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rnk
    FROM products
) ranked
WHERE rnk = 1
ORDER BY category, product_name

One row per category

ROW_NUMBER() gives tied products positions 1 and 2 in whatever order the engine reads them. rn = 1 keeps one of them, and which one can change between runs.

Every product at the top price

DENSE_RANK() gives both position 1, so rnk = 1 keeps both. On this catalog the two queries return the same 11 rows; only this one stays correct when a price ties.

Common follow-up questions

• On Postgres, `ORDER BY price DESC` puts `NULL` prices first, and nine products here have no price. What would your query return there, and how do you fix it? (Tests NULL ordering across engines and `NULLS LAST`.)
• The page now wants the top three price points per category, ties included. What changes? (Tests the difference between `RANK` and `DENSE_RANK` once the filter goes past position 1.)
• How would you write this without a window function? (Tests a grouped maximum joined back to `products` on `category` and `price`, which also keeps ties.)