Difficulty: Intermediate (early)
Estimated time: 25–35 minutes
SQL concepts: correlated subquery, tie-handling, optional window functions (ROW_NUMBER)
Goal: Return the top product per category by price, with deterministic tie-breaking.
The Challenge
The product manager asks:
“For each category, which product is the most expensive?”
This is a classic “top per group” problem—one of the first patterns that pushes learners beyond simple GROUP BY.
Database Table
products
| column_name | type | description |
|---|---|---|
| product_id | INTEGER | Unique product ID |
| name | TEXT | Product name |
| category | TEXT | Product category |
| price | DECIMAL | Product price |
Sample Data (includes a tie)
| product_id | name | category | price |
|---|---|---|---|
| 101 | Wireless Mouse | Accessories | 24.99 |
| 102 | Mechanical Keyboard | Accessories | 89.00 |
| 103 | Pro Keyboard | Accessories | 89.00 |
| 104 | Monitor | Displays | 229.99 |
| 105 | 34-inch Monitor | Displays | 399.00 |
| 106 | Laptop Stand | Workspace | 39.99 |
| 107 | Chair Mat | Workspace | 79.99 |
Your Task
Return one row per category with:
categoryname(top product)price(top price)
Tie rule (required):
If multiple products tie for the highest price in a category, return the one with the lowest product_id (deterministic output).
Sort the final results by:
category(A–Z)
Expected Output (based on sample data)
Accessories has a tie at 89.00 (product_id 102 and 103), so choose 102.
| category | name | price |
|---|---|---|
| Accessories | Mechanical Keyboard | 89.00 |
| Displays | 34-inch Monitor | 399.00 |
| Workspace | Chair Mat | 79.99 |
Constraints
- Do not use
SELECT * - Must return exactly one row per category
- Must implement the tie-break rule (lowest
product_id)
Hint (Optional)
A “top per group” query typically needs one of these approaches:
- Correlated subquery (very compatible, works broadly)
- Window function like
ROW_NUMBER()(cleanest, but requires engine support)
SELECT
p.category,
p.name,
p.price
FROM products p
WHERE p.product_id = (
SELECT p2.product_id
FROM products p2
WHERE p2.category = p.category
ORDER BY p2.price DESC, p2.product_id ASC
LIMIT 1
)
ORDER BY p.category ASC;
SELECT
category,
name,
price
FROM (
SELECT
category,
name,
price,
ROW_NUMBER() OVER (
PARTITION BY category
ORDER BY price DESC, product_id ASC
) AS rn
FROM products
) ranked
WHERE rn = 1
ORDER BY category ASC;
Remarks on Possible Solution A
This version works reliably even when window functions are not available.
Why this works
- For each product row
p, the inner query finds the single best product_id in that category by:- highest
price - lowest
product_idas tie-break
- highest
- The outer query keeps only the row whose
product_idmatches that “winner.”
Remarks on Possible Solution B
Use this if your SQL engine supports window functions.
Why this works
ROW_NUMBER()assigns ranks within each category.- The row ranked
1is the top product per category using your tie-break rule.
🔑 Key Insight
Top-per-group requires row-level results with group-level logic.
GROUP BYalone can tell you the max price per category, but it can’t directly return the corresponding product row without an additional pattern.
Optional Extension (Mini Bonus)
- Return the top 2 products per category (requires
ROW_NUMBER()or a more advanced approach). - Add a column showing the difference between the top price and the category average.
- Return categories where the top product is at least 2× the category average.
🔗 Next Intermediate Challenge
Intermediate Challenge #3: Second-Highest Product per Category (and why it’s trickier than it looks)
🔗 View reference solution on GitHub
(After you’ve tried the challenge)
Want more practical SQL challenges?
Subscribe to the Solve With SQL newsletter and get new problems delivered to your inbox.
SolveWithSQL 