Price Rank

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

Problem

Give each product in products its price position within its own category, the most expensive at 1, where products at the same price share a position and the next price down takes the very next number. Products with no listed price have no position and stay out of the list.

In every category, someone charges the most. Who's on top?

Tables

products

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

Expected output

Worked solution and explanation

This is a per-group numbering problem dressed up as merchandising. Anyone can sort products by price descending. What separates candidates is restarting the count inside every category and keeping unpriced products out of it. Number across the whole catalog and 'Eco Widget v97', the priciest 'Automotive' product at 1831.91, comes back at 4 instead of 1, because three pricier products in other categories sit above it. Leave the 9 unpriced products in and you return 9 rows the answer does not have.

The statement also promises a tie rule: equal prices share a position, and the next price takes the very next number. No category on this seed repeats a price, so the grader cannot tell the tie-handling functions apart. Your interviewer can, and the contract you were given names exactly one of them.

Three decisions, in order

Step 1: Remove unpriced products before numbering

WHERE price IS NOT NULL runs before the window, so the 9 products with no price never take a number. Keep the filter exactly that narrow: product_id 1246 in 'Beauty' is a free sample at 0.0, which is a real price, so it stays and lands last in its category. A price > 0 filter would quietly lose it.

Step 2: Restart the count per category

PARTITION BY category gives every category its own sequence starting at 1. 'Collectibles' holds only two products, so it reads 1 and 2. 'Automotive' starts at 'Eco Widget v97' rather than inheriting a global 4.

Step 3: Pick the function the tie contract names

DENSE_RANK() shares a position across equal prices and skips nothing after them. RANK() would jump from 2 to 4 after a two-way tie, and ROW_NUMBER() would split the tied pair arbitrarily. On this seed all three agree; the statement is what decides.

SELECT product_name, category, price, rnk AS position
FROM (
    SELECT
        product_name,
        category,
        price,
        DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rnk
    FROM products
    WHERE price IS NOT NULL
) ranked

One catalog-wide sequence

DENSE_RANK() OVER (ORDER BY price DESC) numbers all 191 priced products once. 'Eco Widget v97' gets 4, and most categories never see a 1.

A sequence per category

DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) restarts at every category boundary. 'Eco Widget v97' gets 1, and so does each category's priciest product.

Common follow-up questions

• Return only the top three positions per category. Can that produce more than three rows for a category? (Filtering on `position <= 3` after the window, and whether shared positions let extra rows through.)
• Merchandising wants positions among in-stock products only. Where does `in_stock = 1` go, and does it change anyone else's number? (Filtering before the window versus after it.)
• If two products tie on `price`, how would you order them by `rating` without giving them different positions? (The difference between the window's ORDER BY and a presentation sort.)