SQL interview question: top 3 categories in each city
Problem
Shared data rulesFor each city, find the three categories with the highest revenue in September 2026. Equal revenue shares a place, so a city can have more than three rows.
Formula
rnk = 1 + number of the city’s categories with higher revenue
Example
orders| order_id | status | city | created_at |
|---|---|---|---|
| 701 | delivered | Boston | 2026-09-01 10:15 |
| 702 | delivered | Boston | 2026-09-10 15:00 |
order_items| order_id | category | quantity | price_usd |
|---|---|---|---|
| 701 | Meat & seafood | 1 | 18 |
| 701 | Produce | 4 | 3 |
| 701 | Sweets & snacks | 1 | 10 |
| 702 | Beverages | 2 | 5 |
| 702 | Pantry | 1 | 4 |
| city | category | revenue | rnk |
|---|---|---|---|
| Boston | Meat & seafood | 18 | 1 |
| Boston | Produce | 12 | 2 |
| Boston | Beverages | 10 | 3 |
| Boston | Sweets & snacks | 10 | 3 |
Boston’s revenue: meat $18, produce 4 × $3 = $12, beverages 2 × $5 and sweets — $10 each, so they share 3rd place. Pantry at $4 is fifth and isn’t in the answer.
Solve it free
Your query runs on PostgreSQL in your browser. No sign-up needed.