Best-Selling Dish of Each Restaurant — SQL Problem
Each restaurant's page shows a "Bestseller" tag on the dish that has sold the most units across all orders (the sum of quantity).
- Difficulty: Medium
- Topics: Window Functions, Joins, Aggregation
- Dialect: MySQL
- Problem: #158
Problem statement
Each restaurant's page shows a "Bestseller" tag on the dish that has sold the most units across all orders (the sum of quantity). If several dishes of a restaurant tie for the most units, they all get the tag. Dishes that were never ordered cannot be bestsellers, and a restaurant with no sales gets no row.
Return restaurant (the restaurant's name), item_name and units_sold for every bestseller. Return the rows in any order.
Tables
Table: Restaurant
| Column | Type |
|---|---|
| restaurant_id | int |
| name | varchar |
Primary key: restaurant_id.
One row per restaurant.
Table: MenuItem
| Column | Type |
|---|---|
| item_id | int |
| restaurant_id | int |
| item_name | varchar |
| price | int |
Primary key: item_id.
One row per dish; restaurant_id is always in Restaurant.
Table: OrderLine
| Column | Type |
|---|---|
| order_id | int |
| item_id | int |
| quantity | int |
Primary key: order_id, item_id.
One row per dish in an order; item_id is always in MenuItem.
Examples
Example 1
Restaurant
| restaurant_id | name |
|---|---|
| 1 | Paratha Point |
| 2 | Wok Express |
| 3 | Green Bowl |
MenuItem
| item_id | restaurant_id | item_name | price |
|---|---|---|---|
| 10 | 1 | Chole Bhature | 160 |
| 11 | 1 | Dal Makhani | 210 |
| 12 | 1 | Gulab Jamun | 70 |
| 20 | 2 | Veg Hakka Noodles | 180 |
| 21 | 2 | Veg Momos | 120 |
| 30 | 3 | Pav Bhaji | 140 |
OrderLine
| order_id | item_id | quantity |
|---|---|---|
| 1 | 10 | 2 |
| 1 | 12 | 4 |
| 2 | 11 | 3 |
| 3 | 10 | 2 |
| 4 | 20 | 1 |
| 4 | 21 | 3 |
| 5 | 20 | 2 |
Output
| restaurant | item_name | units_sold |
|---|---|---|
| Paratha Point | Chole Bhature | 4 |
| Paratha Point | Gulab Jamun | 4 |
| Wok Express | Veg Hakka Noodles | 3 |
| Wok Express | Veg Momos | 3 |
How to solve Best-Selling Dish of Each Restaurant
Two steps. First an ordinary aggregate: join OrderLine to MenuItem and group by dish to get each dish's SUM(quantity). The inner join already drops dishes with no sales, so they can never be the top of anything.
Second, find the top per restaurant. A window over the grouped rows does it in the same query — window functions run after GROUP BY, so RANK() OVER (PARTITION BY restaurant_id ORDER BY SUM(quantity) DESC) ranks the dish totals within each restaurant. RANK (or DENSE_RANK) gives every dish that ties for the most units rank 1, which is exactly the "they all get the tag" rule; ROW_NUMBER would pick one of them arbitrarily. Filter rnk = 1 outside, and join Restaurant for the name.
Equivalent forms compare each dish total with the restaurant's maximum: a correlated MAX over the CTE, or MAX(SUM(quantity)) OVER (PARTITION BY restaurant_id). Group by item_id, not by name — names repeat across restaurants. The cost is one aggregation of the order lines and a sort per restaurant.
Reference solution (MySQL)
WITH sold AS (
SELECT m.restaurant_id, m.item_name, SUM(o.quantity) AS units_sold,
RANK() OVER (PARTITION BY m.restaurant_id ORDER BY SUM(o.quantity) DESC) AS rnk
FROM MenuItem m
JOIN OrderLine o ON o.item_id = m.item_id
GROUP BY m.item_id, m.restaurant_id, m.item_name
)
SELECT r.name AS restaurant, s.item_name, s.units_sold
FROM sold s
JOIN Restaurant r ON r.restaurant_id = s.restaurant_id
WHERE s.rnk = 1Another way
WITH sold AS (
SELECT m.restaurant_id, m.item_name, SUM(o.quantity) AS units_sold
FROM MenuItem m JOIN OrderLine o ON o.item_id = m.item_id
GROUP BY m.item_id, m.restaurant_id, m.item_name
)
SELECT r.name AS restaurant, s.item_name, s.units_sold
FROM sold s JOIN Restaurant r ON r.restaurant_id = s.restaurant_id
WHERE s.units_sold = (SELECT MAX(t.units_sold) FROM sold t WHERE t.restaurant_id = s.restaurant_id)Another way
SELECT r.name AS restaurant, x.item_name, x.units_sold
FROM (SELECT m.restaurant_id, m.item_name, SUM(o.quantity) AS units_sold,
MAX(SUM(o.quantity)) OVER (PARTITION BY m.restaurant_id) AS top_units
FROM MenuItem m JOIN OrderLine o ON o.item_id = m.item_id
GROUP BY m.item_id, m.restaurant_id, m.item_name) x
JOIN Restaurant r ON r.restaurant_id = x.restaurant_id
WHERE x.units_sold = x.top_units← Peak-Hour Share of Orders by City · Orders Above the Customer's Own Average Basket →