Best-Selling Dish of Each Restaurant

Medium
158.

Best-Selling Dish of Each Restaurant

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

ColumnType
restaurant_idint
namevarchar

Primary key: restaurant_id.

One row per restaurant.

Table: MenuItem

ColumnType
item_idint
restaurant_idint
item_namevarchar
priceint

Primary key: item_id.

One row per dish; restaurant_id is always in Restaurant.

Table: OrderLine

ColumnType
order_idint
item_idint
quantityint

Primary key (together): order_id, item_id.

One row per dish in an order; item_id is always in MenuItem.

Example

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

Restaurant

restaurant_idname
1Paratha Point
2Wok Express
3Green Bowl

MenuItem

item_idrestaurant_iditem_nameprice
101Chole Bhature160
111Dal Makhani210
121Gulab Jamun70
202Veg Hakka Noodles180
212Veg Momos120
303Pav Bhaji140

OrderLine

order_iditem_idquantity
1102
1124
2113
3102
4201
4213
5202

Output

restaurantitem_nameunits_sold
Paratha PointChole Bhature4
Paratha PointGulab Jamun4
Wok ExpressVeg Hakka Noodles3
Wok ExpressVeg Momos3
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.

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

ColumnType
restaurant_idint
namevarchar

Primary key: restaurant_id.

One row per restaurant.

Table: MenuItem

ColumnType
item_idint
restaurant_idint
item_namevarchar
priceint

Primary key: item_id.

One row per dish; restaurant_id is always in Restaurant.

Table: OrderLine

ColumnType
order_idint
item_idint
quantityint

Primary key: order_id, item_id.

One row per dish in an order; item_id is always in MenuItem.

Examples

Example 1

Restaurant

restaurant_idname
1Paratha Point
2Wok Express
3Green Bowl

MenuItem

item_idrestaurant_iditem_nameprice
101Chole Bhature160
111Dal Makhani210
121Gulab Jamun70
202Veg Hakka Noodles180
212Veg Momos120
303Pav Bhaji140

OrderLine

order_iditem_idquantity
1102
1124
2113
3102
4201
4213
5202

Output

restaurantitem_nameunits_sold
Paratha PointChole Bhature4
Paratha PointGulab Jamun4
Wok ExpressVeg Hakka Noodles3
Wok ExpressVeg Momos3

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

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