High-Value Electronics
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
In products, plenty of electronics are listed that are not currently on hand. Of the electronics that are in stock, show the name and price of the five most expensive, highest price first.
The five priciest electronics.
Tables
products
Columns: product_id INTEGER, product_name TEXT, category TEXT, price REAL, rating REAL, in_stock INTEGER
| product_id | product_name | category | price | rating | in_stock |
|---|---|---|---|---|---|
| 1001 | Basic Device 1X | Books | 17.52 | 2.7 | 1 |
| 1050 | Deluxe Bundle 2X | Clothing | 25.05 | 4.4 | 1 |
| 1099 | Pro Unit 3X | Home & Kitchen | 32.58 | 2.1 | 1 |
| 1148 | Ultra Tool 4X | Collectibles | 40.11 | 3.8 | 1 |
| 1197 | Essential Set 5X | Toys | 47.64 | 1.5 | 1 |
| 1246 | Classic System 6X | Beauty | 0 | 3.2 | 1 |
| 1295 | Smart Gadget 7X | Automotive | 62.7 | 4.9 | 0 |
| 1344 | Eco Kit 8X | Garden | 70.23 | 2.6 | 1 |
| 1393 | Turbo Pack 9X | Music | 77.76 | 4.3 | 0 |
| 1442 | Premium Widget 10X | Electronics | 85.29 | 2 | 1 |
Expected output
| product_name | price |
|---|---|
| Basic Set v100 | 1859.72 |
| Basic Set v80 | 1674.32 |
| Basic Set v70 | 1581.62 |
| Basic Set v60 | 1488.92 |
| Basic Set v50 | 1396.22 |
Worked solution and explanation
What this really is
This is a two-condition filter followed by a sorted cut, wearing a shopping costume. Everyone gets the category right. What separates candidates is whether 'in stock' registers as a requirement or slides past as scenery. On this seed some of the most expensive electronics are not on hand, so a query that filters on category alone gives one of them a top-five spot and quietly drops a real on-hand item off the bottom.
Step 1: Keep only rows that pass both conditions
Put category = 'Electronics' and in_stock = 1 in the same WHERE, joined with AND. Both must hold for a row to survive. An OR here would pull in every in-stock book and every out-of-stock gadget.
Step 2: Sort by price from highest down
ORDER BY price DESC puts the costliest survivors first. The prompt asks for the most expensive, and the preview reads top to bottom from the highest price, so the direction is not optional.
Step 3: Cut at five, after the sort
LIMIT 5 runs after the sort, so it keeps the top five rather than whichever five the scan reached first. Project only product_name and price: the preview shows two columns, and a SELECT * returns six.
SELECT product_name, price
FROM products
WHERE category = 'Electronics'
AND in_stock = 1
ORDER BY price DESC
LIMIT 5- Category only
WHERE category = 'Electronics'with the same sort and cap. Looks right, runs clean, and lets an out-of-stock item take a slot near the top.- Category and stock
WHERE category = 'Electronics' AND in_stock = 1. The out-of-stock item is gone before the sort, so all five slots go to items that can actually be stocked.
Common follow-up questions
- How would you return the top five for every category at once? (Moves the cut inside each group, typically with `ROW_NUMBER()` partitioned by `category`.)
- Two items tie on price at the fifth spot. How do you make the result deterministic? (Adds a tie-break key such as `product_id` to the `ORDER BY`.)
- What changes if this runs on Postgres and `price` can be `NULL`? (Tests knowledge of `NULLS LAST` under a descending sort.)