Difficulty: Intermediate
Estimated time: 30–40 minutes
SQL concepts: correlated subqueries, ordering, tie awareness, optional window functions
Goal: Return the second-highest priced product per category, reliably and deterministically.
The Challenge
The product manager asks:
“For each category, which product is the second most expensive?”
This sounds like a small change from “top product per category,”
but it introduces a much harder reasoning problem.
Why?
- You can’t use
MAX()directly - You must think carefully about ordering and offsets
- You must decide how to handle ties
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 ties)
| product_id | name | category | price |
|---|---|---|---|
| 201 | Wireless Mouse | Accessories | 24.99 |
| 202 | Mechanical Keyboard | Accessories | 89.00 |
| 203 | Pro Keyboard | Accessories | 89.00 |
| 204 | USB-C Hub | Accessories | 34.50 |
| 205 | Monitor | Displays | 229.99 |
| 206 | 34-inch Monitor | Displays | 399.00 |
| 207 | Desk Lamp | Workspace | 49.99 |
| 208 | Chair Mat | Workspace | 79.99 |
Your Task
Return one row per category with:
categorynameprice
Where the product is the second-highest priced in that category.
Tie rule (required)
If multiple products share the same price:
- Order by
price DESC - Then by
product_id ASC - The second row after sorting is the answer
Sort final results by:
category(A–Z)
Expected Output (based on sample data)
Accessories sorted by price DESC:
- 202 → 89.00
- 203 → 89.00 ← second
- 204 → 34.50
- 201 → 24.99
Displays:
- 206 → 399.00
- 205 → 229.99
Workspace:
- 208 → 79.99
- 207 → 49.99
| category | name | price |
|---|---|---|
| Accessories | Pro Keyboard | 89.00 |
| Displays | Monitor | 229.99 |
| Workspace | Desk Lamp | 49.99 |
Constraints
- Do not use
SELECT * - Must return exactly one row per category
- Must follow the tie rule
- Must work reliably even with equal prices
Hint (Optional)
This problem is much easier if you:
- Sort products within each category
- Assign an order
- Pick the row ranked
2
You can do this with:
- a correlated subquery (SQLite-safe)
- or a window function (cleaner, if supported)
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 OFFSET 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 = 2
ORDER BY category ASC;
Why this works (Solution A)
- The inner query:
- sorts products per category
- skips the first (highest)
- selects the second
- The outer query returns only the matching row
Explanation
ROW_NUMBER()assigns an order within each category- The second-highest product always receives
rn = 2 - Tie-breaking is handled explicitly
- Output is stable and predictable
This pattern generalizes to:
- 3rd-highest
- top-N per group
- percentile-based analysis
🔑 Key Insight
Finding “second best” is harder than “best.”
Once you can solve this, you’re genuinely operating at an intermediate level.
Common Mistakes
- Using
MAX(price)twice (doesn’t work) - Returning multiple rows per category
- Forgetting deterministic tie ordering
- Using
LIMIT 2globally instead of per category
Optional Extension (Mini Bonus)
- Return the third-highest product per category
- Return categories with at least 3 products only
- Show the price difference between highest and second-highest
🔗 Next Intermediate Challenge
Intermediate Challenge #4: Products Above Category Median (why median is trickier than average)
🔗 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 