Top of the Line

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

Asked in

Tata Consultancy Services

Problem

The budget corner of the buyer's guide features the five cheapest items in products, and the merchandiser laying it out wants them lowest price first. Show each item's name, category, price and whether it's in stock; an item with no listed price isn't a candidate.

The five priciest items for the luxury section.

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 is really testing

Strip the buyer's-guide costume and this is a top-N cut over a column with holes in it. Everyone writes the sort and the LIMIT. What separates answers is whether you ask what a missing price does to a lowest-first sort. On the engine grading this, a NULL sorts ahead of every real number when you go ascending, so a query without the filter proudly reports unpriced products as the cheapest things in the catalog. It runs, it returns five rows, and most of them are wrong.

Three moves, in order

Step 1: Set aside the unpriced products

WHERE price IS NOT NULL says the business rule out loud: an item with no price is not a candidate for 'cheapest'. Do not lean on the engine's NULL placement to do it for you; that placement differs between engines and flips with sort direction.

Step 2: Sort lowest price first

ORDER BY price ASC puts the cheapest at the top. The single product listed at a price of '0' lands first, and it belongs there: zero is a real listed price, not a missing one.

Step 3: Cut at five

LIMIT 5 runs after the sort, so it keeps the five lowest prices rather than an arbitrary five that happen to be displayed in order.

The solution

SELECT product_name, category, price, in_stock
FROM products
WHERE price IS NOT NULL
ORDER BY price ASC
LIMIT 5

Without the filter

ORDER BY price ASC LIMIT 5 on its own: the unpriced rows sort to the front, so the cut is mostly NULL prices and the real cheapest items fall off the list.

With the filter

WHERE price IS NOT NULL first: only priced products reach the sort, and the five rows are the five lowest listed prices, starting with the free item.

Common follow-up questions

• What would the result look like if you replaced the filter with `COALESCE(price, 0)`? (Tests whether the candidate sees that defaulting a missing price to zero turns unpriced items into free ones.)
• How would you return the five cheapest per `category` instead of overall? (Tests the jump to a per-group ranking once the cut has to restart inside each category.)
• If two products share the price at the fifth slot, which one makes the list, and how would you return both? (Tests awareness that `LIMIT` cuts arbitrarily inside a tie, and that a tiebreak key or a with-ties fetch is the fix.)