The Sweet Spot

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

Airbnb

Problem

A budget-conscious shopper is comparing electronics worth buying: in stock and rated at least 4.0. Walk the list from the most affordable up to the priciest, so the cheapest deal lands at the top, showing just each product's name and its price.

Cheap and highly rated. A rare combination.

Tables

products

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

Expected output

Worked solution and explanation

A filter problem wearing a shopping-list costume

Strip away the shopper and this is a three-condition filter with a sort on top. The seed hides a decoy behind every condition. Anyone can type the WHERE; what separates candidates is reading 'at least 4.0' literally. Premium Widget 30X, the cheapest electronics item worth buying at 235.89, is rated exactly 4.0. Write rating > 4.0 and it vanishes, so your list opens at 1210.82 and the one real bargain is gone. Drop the stock flag instead, and Premium Widget 70X (also rated 4.0, but out of stock) slides in second at 537.09.

The three conditions and the rows they remove

Step 1: Stack the conditions in one WHERE

All three must hold at once, so they join with AND, and each earns its place against a specific row. The category match removes Deluxe Bundle 2X, a 4.4-rated Clothing item at 25.05 that would otherwise top the list. in_stock = 1 removes Premium Widget 70X. The rating floor removes 15 of the 20 electronics rows.

Step 2: Keep 4.0 on the inside of the boundary

'At least' means >=. Two electronics rows are rated exactly 4.0, so the boundary is not hypothetical: it decides whether Premium Widget 30X makes the list at all. in_stock is a 0/1 integer flag, so compare it to 1, not to a string like 'yes'.

Step 3: Match the label, sort cheapest first, return two columns

category LIKE '%Electronics%' keeps any label that mentions the word. The labels in this table are clean single values, so category = 'Electronics' returns the same three rows; the pattern is the tolerant choice, not a fix for dirty data. ORDER BY price ASC puts the cheapest qualifier first, and the result is just product_name and price. There is no LIMIT, because the shopper wants every qualifier.

SELECT product_name, price
FROM products
WHERE rating >= 4.0
  AND in_stock = 1
  AND category LIKE '%Electronics%'
ORDER BY price ASC

Strict cutoff

rating > 4.0 returns Basic Set v30 (1210.82) and Basic Set v70 (1581.62). It looks complete, two clean rows sorted cheapest first, and it is wrong.

Inclusive floor

rating >= 4.0 adds Premium Widget 30X at 235.89 at the top. The cheapest deal is exactly the row the strict version silently dropped.

Common follow-up questions

• If the shopper only wanted the single cheapest qualifier, how would you handle two products tied at the lowest price? (Tests `FETCH FIRST 1 ROW WITH TIES` or a rank filter instead of a bare `LIMIT 1`.)
• What if categories were stored inconsistently, like 'ELECTRONICS' and 'electronics'? (Tests `LOWER(category)` or a case-insensitive match before the comparison.)
• How does your filter treat products whose `rating` is `NULL`, and what would you change to list them separately? (Tests three-valued logic: `NULL >= 4.0` is unknown, so `WHERE` drops the row without an explicit check.)