Restaurants Cooking Faster Than Their City Average
A kitchen ticket's preparation time is TIMESTAMPDIFF(MINUTE, accepted_at, ready_at).
- Difficulty: Medium
- Topics: Subqueries, Joins
- Dialect: MySQL
- Problem: #164
Problem statement
A kitchen ticket's preparation time is TIMESTAMPDIFF(MINUTE, accepted_at, ready_at). The ops team wants restaurants whose average preparation time is strictly lower than the average over all tickets of restaurants in the same city (every ticket weighs the same in the city average).
Return name, city and avg_prep_min (the restaurant's average preparation time, rounded to 2 decimal places) for every such restaurant. Restaurants with no tickets are not considered. Return the rows in any order.
Tables
Table: Restaurant
| Column | Type |
|---|---|
| restaurant_id | int |
| name | varchar |
| city | varchar |
Primary key: restaurant_id.
One row per restaurant.
Table: KitchenTicket
| Column | Type |
|---|---|
| ticket_id | int |
| restaurant_id | int |
| accepted_at | datetime |
| ready_at | datetime |
Primary key: ticket_id.
One row per order a kitchen accepted; ready_at is when the food was packed. restaurant_id is always in Restaurant.
Examples
Example 1
Restaurant
| restaurant_id | name | city |
|---|---|---|
| 1 | Kebab Kothi | Delhi |
| 2 | Paratha Point | Delhi |
| 3 | Chaat Corner | Delhi |
| 4 | Curry Leaf | Kochi |
| 5 | Momo Mansion | Delhi |
KitchenTicket
| ticket_id | restaurant_id | accepted_at | ready_at |
|---|---|---|---|
| 1 | 1 | 2024-04-10 19:00:00 | 2024-04-10 19:20:00 |
| 2 | 1 | 2024-04-10 20:00:00 | 2024-04-10 20:24:00 |
| 3 | 2 | 2024-04-10 19:10:00 | 2024-04-10 19:22:00 |
| 4 | 2 | 2024-04-10 19:40:00 | 2024-04-10 19:55:00 |
| 5 | 2 | 2024-04-10 21:00:00 | 2024-04-10 21:13:00 |
| 6 | 3 | 2024-04-10 18:00:00 | 2024-04-10 18:16:00 |
| 7 | 4 | 2024-04-10 13:00:00 | 2024-04-10 13:15:00 |
Output
| name | city | avg_prep_min |
|---|---|---|
| Paratha Point | Delhi | 13.33 |
| Chaat Corner | Delhi | 16 |
How to solve Restaurants Cooking Faster Than Their City Average
Two averages are compared: one per restaurant, one per city, and the city one is ticket-weighted — the average of all the city's tickets, not the average of its restaurants' averages. In the example Delhi's tickets are 20, 24, 12, 15, 13 and 16 minutes, an average of 16.67; Paratha Point (13.33) and Chaat Corner (16) are faster, Kebab Kothi (22) is not. Averaging the three restaurant averages would give 17.11 instead, and the answer could change.
A CTE that attaches the city and the minutes to each ticket keeps the rest short. Group it by restaurant for the restaurant's average, and in HAVING compare it with a correlated subquery over the same CTE filtered to the restaurant's city. Restaurants without tickets never enter the CTE, and a city's only restaurant equals its city average, so it is never strictly faster.
Aggregating both levels into their own CTEs and comparing total_r × n_c < total_c × n_r avoids division entirely and computes each city once. Either plan is a pass over the tickets plus one group per restaurant.
Reference solution (MySQL)
WITH prep AS (
SELECT r.restaurant_id, r.name, r.city, TIMESTAMPDIFF(MINUTE, k.accepted_at, k.ready_at) AS mins
FROM KitchenTicket k JOIN Restaurant r ON r.restaurant_id = k.restaurant_id
)
SELECT p.name, p.city, ROUND(AVG(p.mins), 2) AS avg_prep_min
FROM prep p
GROUP BY p.restaurant_id, p.name, p.city
HAVING AVG(p.mins) < (SELECT AVG(c.mins) FROM prep c WHERE c.city = p.city)Another way
WITH prep AS (
SELECT r.restaurant_id, r.name, r.city, TIMESTAMPDIFF(MINUTE, k.accepted_at, k.ready_at) AS mins
FROM KitchenTicket k JOIN Restaurant r ON r.restaurant_id = k.restaurant_id
), per_restaurant AS (
SELECT restaurant_id, name, city, SUM(mins) AS total, COUNT(*) AS n FROM prep GROUP BY restaurant_id, name, city
), per_city AS (
SELECT city, SUM(mins) AS total, COUNT(*) AS n FROM prep GROUP BY city
)
SELECT p.name, p.city, ROUND(p.total / p.n, 2) AS avg_prep_min
FROM per_restaurant p JOIN per_city c ON c.city = p.city
WHERE p.total * c.n < c.total * p.n← Coupon Discount Spend by Campaign Prefix · Customers Whose Second Order Came Within a Week →