Zomato: Top Intercity Legends Dish in Each Destination City
Intercity Legends ships iconic dishes from one city to customers in another. The category team wants the most-loved import in every city it delivers to.
- Difficulty: Medium
- Topics: Window Functions, Joins, Aggregation
- Dialect: MySQL
- Problem: #1039
Problem statement
Intercity Legends ships iconic dishes from one city to customers in another. The category team wants the most-loved import in every city it delivers to.
Count the quantity of delivered orders per destination city and dish. An order delivered to the dish's own origin_city is not an intercity order and does not count. For each destination_city, return the dish(es) with the most units — all of them if tied — with destination_city, dish_name, origin_city and units. Return the rows in any order.
A practice scenario set at Zomato: the tables and data are invented, and this is not a question from, or endorsed by, Zomato.
Tables
Table: LegendsDish
| Column | Type |
|---|---|
| dish_id | int |
| dish_name | varchar |
| origin_city | varchar |
Primary key: dish_id.
One row per dish on Intercity Legends, with the city it is cooked in.
Table: LegendsOrder
| Column | Type |
|---|---|
| order_id | int |
| dish_id | int |
| destination_city | varchar |
| quantity | int |
| status | enum(delivered, cancelled) |
Primary key: order_id.
One row per order line of one dish.
Examples
Example 1
LegendsDish
| dish_id | dish_name | origin_city |
|---|---|---|
| 1 | Kolkata Rosogolla | Kolkata |
| 2 | Lucknowi Galouti | Lucknow |
| 3 | Hyderabadi Biryani | Hyderabad |
| 4 | Jaipur Ghewar | Jaipur |
LegendsOrder
| order_id | dish_id | destination_city | quantity | status |
|---|---|---|---|---|
| 1 | 1 | Bengaluru | 4 | delivered |
| 2 | 3 | Bengaluru | 3 | delivered |
| 3 | 3 | Bengaluru | 2 | delivered |
| 4 | 2 | Delhi | 2 | delivered |
| 5 | 4 | Delhi | 2 | delivered |
| 6 | 1 | Delhi | 5 | cancelled |
| 7 | 3 | Hyderabad | 10 | delivered |
| 8 | 1 | Hyderabad | 1 | delivered |
Output
| destination_city | dish_name | origin_city | units |
|---|---|---|---|
| Bengaluru | Hyderabadi Biryani | Hyderabad | 5 |
| Delhi | Lucknowi Galouti | Lucknow | 2 |
| Delhi | Jaipur Ghewar | Jaipur | 2 |
| Hyderabad | Kolkata Rosogolla | Kolkata | 1 |
How to solve Zomato: Top Intercity Legends Dish in Each Destination City
Two steps: total, then pick the top per group. The totals come from joining each order line to its dish, keeping delivered lines whose destination_city differs from the dish's origin_city, and summing quantity per destination and dish. The origin test is why the join comes before the aggregation — a Hyderabadi Biryani sold in Hyderabad is ordinary delivery, not an Intercity Legends import.
Picking the top is a per-partition ranking: DENSE_RANK() OVER (PARTITION BY destination_city ORDER BY units DESC) gives the largest total in each city position 1, and equal totals share it, so ties all come back. A city reached only by cancelled orders or by orders of its own dishes has no totals and no row.
The alternative joins the totals to each city's MAX(units) and keeps the rows that equal it — the same result without window functions, at the cost of reading the CTE twice. Group by dish_id as well as the name so two dishes with the same name would stay apart.
Reference solution (MySQL)
WITH units AS (
SELECT o.destination_city, d.dish_name, d.origin_city, SUM(o.quantity) AS units
FROM LegendsOrder o
JOIN LegendsDish d ON d.dish_id = o.dish_id
WHERE o.status = 'delivered' AND o.destination_city <> d.origin_city
GROUP BY o.destination_city, d.dish_id, d.dish_name, d.origin_city
),
ranked AS (
SELECT destination_city, dish_name, origin_city, units,
DENSE_RANK() OVER (PARTITION BY destination_city ORDER BY units DESC) AS pos
FROM units
)
SELECT destination_city, dish_name, origin_city, units FROM ranked WHERE pos = 1Another way
WITH units AS (
SELECT o.destination_city, d.dish_name, d.origin_city, SUM(o.quantity) AS units
FROM LegendsOrder o JOIN LegendsDish d ON d.dish_id = o.dish_id
WHERE o.status = 'delivered' AND o.destination_city <> d.origin_city
GROUP BY o.destination_city, d.dish_id, d.dish_name, d.origin_city
)
SELECT u.destination_city, u.dish_name, u.origin_city, u.units
FROM units u
JOIN (SELECT destination_city, MAX(units) AS best FROM units GROUP BY destination_city) m
ON m.destination_city = u.destination_city AND m.best = u.units← Zomato: Table Reservation No-Show Rate by Meal Slot · Zomato: September Photo Decisions of Every Moderator →