Zomato: Top Intercity Legends Dish in Each Destination City

Medium
1039.

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.

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

ColumnType
dish_idint
dish_namevarchar
origin_cityvarchar

Primary key: dish_id.

One row per dish on Intercity Legends, with the city it is cooked in.

Table: LegendsOrder

ColumnTypeValues
order_idint
dish_idint
destination_cityvarchar
quantityint
statusenumdelivered, cancelled

Primary key: order_id.

One row per order line of one dish.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

LegendsDish

dish_iddish_nameorigin_city
1Kolkata RosogollaKolkata
2Lucknowi GaloutiLucknow
3Hyderabadi BiryaniHyderabad
4Jaipur GhewarJaipur

LegendsOrder

order_iddish_iddestination_cityquantitystatus
11Bengaluru4delivered
23Bengaluru3delivered
33Bengaluru2delivered
42Delhi2delivered
54Delhi2delivered
61Delhi5cancelled
73Hyderabad10delivered
81Hyderabad1delivered

Output

destination_citydish_nameorigin_cityunits
BengaluruHyderabadi BiryaniHyderabad5
DelhiLucknowi GaloutiLucknow2
DelhiJaipur GhewarJaipur2
HyderabadKolkata RosogollaKolkata1
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
dish_idint
dish_namevarchar
origin_cityvarchar

Primary key: dish_id.

One row per dish on Intercity Legends, with the city it is cooked in.

Table: LegendsOrder

ColumnType
order_idint
dish_idint
destination_cityvarchar
quantityint
statusenum(delivered, cancelled)

Primary key: order_id.

One row per order line of one dish.

Examples

Example 1

LegendsDish

dish_iddish_nameorigin_city
1Kolkata RosogollaKolkata
2Lucknowi GaloutiLucknow
3Hyderabadi BiryaniHyderabad
4Jaipur GhewarJaipur

LegendsOrder

order_iddish_iddestination_cityquantitystatus
11Bengaluru4delivered
23Bengaluru3delivered
33Bengaluru2delivered
42Delhi2delivered
54Delhi2delivered
61Delhi5cancelled
73Hyderabad10delivered
81Hyderabad1delivered

Output

destination_citydish_nameorigin_cityunits
BengaluruHyderabadi BiryaniHyderabad5
DelhiLucknowi GaloutiLucknow2
DelhiJaipur GhewarJaipur2
HyderabadKolkata RosogollaKolkata1

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 = 1

Another 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 →