Bargain Bin

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

We're prepping a vendor call to reorder products that have sold out, but only the ones customers have actually rated. For each category, report the price floor, the ceiling, and the gap between the two.

Floor prices. Right before the vendor call.

Tables

products

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

Expected output

Worked solution and explanation

The filter that does the real work

This looks like a MIN/MAX exercise, and the aggregation is the easy half. The real work is the scope: the vendor call is about products that have sold out, and only the ones customers bothered to rate. Run MIN(price) and MAX(price) grouped by category over the raw table and you get honest-looking numbers built from the wrong rows, an in-stock item you are not reordering setting a category's ceiling, or an unrated product nobody reviewed setting its floor. Get the WHERE right and the aggregation falls out; get it wrong and every floor and ceiling in the report describes products that were never in scope. The other classic miss is reaching for product_name in the SELECT without grouping on it: strict engines reject it, lax ones staple an arbitrary product's name to the wrong price.

Building it

Step 1: Scope to the rows that count

Two filters, and both drop rows that would otherwise move the numbers. in_stock = 0 keeps only the sold-out products worth reordering, so an in-stock item can't set a category's floor or ceiling. rating IS NOT NULL drops the products nobody reviewed; an unrated product still carries a real price, so if it is the cheapest or dearest in its category, leaving it in reports the wrong extreme. Note this is filtering whole rows, a different job from MIN and MAX skipping a NULL value on their own.

Step 2: Collapse each category to one row

GROUP BY category sets the grain to one row per category over the surviving rows. MIN(price) becomes the floor, MAX(price) the ceiling, and MAX(price) - MIN(price) the spread, each computed within that category rather than across the whole catalog.

Step 3: Lead with the widest spread

ORDER BY price_spread DESC puts the categories with the biggest gap between cheapest and dearest at the top, which is exactly what the vendor call wants to see first. Add category as a tiebreaker so two categories with an identical spread come back in a stable, predictable order.

The solution

SELECT category,
       MIN(price) AS floor_price,
       MAX(price) AS ceiling_price,
       MAX(price) - MIN(price) AS price_spread
FROM products
WHERE in_stock = 0 AND rating IS NOT NULL
GROUP BY category

Aggregate first

SELECT category, MIN(price), MAX(price) FROM products GROUP BY category counts every row: an in-stock item or an unreviewed product can set a category's floor or ceiling, so the numbers describe products you would never put on the reorder.

Scope first, then aggregate

Filtering WHERE in_stock = 0 AND rating IS NOT NULL first recomputes the floor and ceiling over only the rows in scope, which is the number the vendor call actually needs.

Common follow-up questions

• How does WHERE `in_stock` = 0 AND rating IS NOT NULL change the result versus aggregating the whole table, and why is that different from letting MIN and MAX skip NULL prices? (Tests whether the candidate sees that filtering whole rows on `in_stock` and rating removes rows whose valid prices would otherwise set the floor or ceiling, which is different from MIN/MAX skipping a NULL value.)
• Two categories come back with the same spread. What decides their relative order, and how would you make it deterministic? (Tests awareness that ORDER BY on a non-unique computed value is non-deterministic without a tiebreaker.)
• Procurement now wants only categories where the spread exceeds 500. Where does that filter go, and why can't it live in the WHERE clause? (Tests understanding that a filter on an aggregate belongs in HAVING, not WHERE.)