Restaurants Cooking Faster Than Their City Average

Medium
164.

Restaurants Cooking Faster Than Their City Average

MediumSubqueriesJoins

MySQL

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

ColumnType
restaurant_idint
namevarchar
cityvarchar

Primary key: restaurant_id.

One row per restaurant.

Table: KitchenTicket

ColumnType
ticket_idint
restaurant_idint
accepted_atdatetime
ready_atdatetime

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.

Example

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

Restaurant

restaurant_idnamecity
1Kebab KothiDelhi
2Paratha PointDelhi
3Chaat CornerDelhi
4Curry LeafKochi
5Momo MansionDelhi

KitchenTicket

ticket_idrestaurant_idaccepted_atready_at
112024-04-10 19:00:002024-04-10 19:20:00
212024-04-10 20:00:002024-04-10 20:24:00
322024-04-10 19:10:002024-04-10 19:22:00
422024-04-10 19:40:002024-04-10 19:55:00
522024-04-10 21:00:002024-04-10 21:13:00
632024-04-10 18:00:002024-04-10 18:16:00
742024-04-10 13:00:002024-04-10 13:15:00

Output

namecityavg_prep_min
Paratha PointDelhi13.33
Chaat CornerDelhi16
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.

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

ColumnType
restaurant_idint
namevarchar
cityvarchar

Primary key: restaurant_id.

One row per restaurant.

Table: KitchenTicket

ColumnType
ticket_idint
restaurant_idint
accepted_atdatetime
ready_atdatetime

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_idnamecity
1Kebab KothiDelhi
2Paratha PointDelhi
3Chaat CornerDelhi
4Curry LeafKochi
5Momo MansionDelhi

KitchenTicket

ticket_idrestaurant_idaccepted_atready_at
112024-04-10 19:00:002024-04-10 19:20:00
212024-04-10 20:00:002024-04-10 20:24:00
322024-04-10 19:10:002024-04-10 19:22:00
422024-04-10 19:40:002024-04-10 19:55:00
522024-04-10 21:00:002024-04-10 21:13:00
632024-04-10 18:00:002024-04-10 18:16:00
742024-04-10 13:00:002024-04-10 13:15:00

Output

namecityavg_prep_min
Paratha PointDelhi13.33
Chaat CornerDelhi16

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 →