SQL interview question: second-highest price in each group
Problem
Shared data rulesFor each city and category, find the second-highest unit price among items sold in September 2026. Equal prices count as one value; skip a category that has only one price in the city.
Example
orders| order_id | status | city | created_at |
|---|---|---|---|
| 201 | delivered | Boston | 2026-09-03 12:00 |
| 202 | delivered | Boston | 2026-09-12 18:00 |
order_items| order_id | product_id | category | quantity | price_usd | Row note |
|---|---|---|---|---|---|
| 201 | 11 | Meat & seafood | 1 | 17.48 | |
| 202 | 11 | Meat & seafood | 2 | 17.48 | ← same price — one value |
| 202 | 12 | Meat & seafood | 1 | 17.28 | |
| 201 | 31 | Beverages | 1 | 5.98 | |
| 202 | 32 | Beverages | 3 | 5.88 | |
| 201 | 21 | Dairy | 2 | 5.18 | |
| 202 | 21 | Dairy | 1 | 5.18 | ← only $5.18 — no second price |
- Row 2: same price — one value
- Row 7: only $5.18 — no second price
| city | category | second_price_usd |
|---|---|---|
| Boston | Meat & seafood | 17.28 |
| Boston | Beverages | 5.88 |
Meat: $17.48 (twice) and $17.28 — the second price is $17.28. Beverages: $5.98 and $5.88 — the second is $5.88.
Solve it free
Your query runs on PostgreSQL in your browser. No sign-up needed.