High Price Products

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

Google

Problem

The analyst renegotiating supplier contracts keeps finding the priciest items buried mid-list in their review of products, when the most expensive should lead. They only negotiate on products priced above 100 and need each one's full record.

Everything above 100.

Tables

products

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

Expected output

Worked solution and explanation

Strip the procurement story and this is one predicate with a sort direction attached. Nobody gets the WHERE wrong. Candidates lose this on the edges: they hand-pick four columns because nobody negotiates on in_stock, or they write ORDER BY price and forget it sorts ascending. The grade is the full record, all six columns, most expensive first. Trim a column or flip the sort and 180 correct rows still come back wrong.

The three decisions

Step 1: Filter strictly above 100

price > 100 keeps 180 of the 200 rows in products. It also does a second job quietly: the 9 products with a NULL price fail the comparison, because NULL > 100 is unknown, not true, and the free sample at 0.0 never comes close. No IS NOT NULL guard is needed.

Step 2: Keep every column

The full record means all six columns, product_id through in_stock. The grader compares columns as well as rows, so dropping rating or in_stock because they look irrelevant to pricing fails a query whose rows are perfect.

Step 3: Sort most expensive first

ORDER BY price DESC is the whole ordering contract. Order is graded here, so the same 180 rows sorted ascending is a wrong answer. Above 100 no two products share a price on this data, and the grader only checks that rows are sorted by price, so no tiebreaker is owed.

SELECT *
FROM products
WHERE price > 100
ORDER BY price DESC

Trimmed and backwards

SELECT product_id, product_name, price ... ORDER BY price returns the right 180 products, but three columns short and cheapest first: two independent reasons it grades wrong.

Full record, priciest first

SELECT * with ORDER BY price DESC returns every column the team asked for, with 1859.72 on the first row.

Common follow-up questions

• How would you return only the 5 most expensive products? (Adds a `LIMIT`; a tie at the cut would then need a stated rule.)
• How would you restrict this to products currently in stock? (A second predicate on `in_stock`.)
• What if procurement wanted each category's single most expensive product instead? (Moves from a filter to a per-`category` pick.)