Top Two Restaurants per Cuisine by Repeat-Customer Rate

Hard
172.

Top Two Restaurants per Cuisine by Repeat-Customer Rate

Loyalty is measured per restaurant on delivered orders: a repeat customer has placed at least 2 delivered orders there, and the repeat rate is repeat customers divided by distinct customers. Only restaurants with at least 3 distinct customers are ranked. Within each cuisine, the restaurants with the two highest distinct repeat rates are featured — restaurants with equal rates share a place, so more than two can be featured.

Return cuisine, name, customers, repeat_customers and repeat_rate_pct (the rate as a percentage, rounded to 1 decimal place) for every featured restaurant. Rates are compared exactly, not rounded. Return the rows in any order.

Tables

Table: Restaurant

ColumnType
restaurant_idint
namevarchar
cuisinevarchar

Primary key: restaurant_id.

One row per restaurant.

Table: Orders

ColumnTypeValues
order_idint
restaurant_idint
customer_idint
statusenumdelivered, cancelled

Primary key: order_id.

One row per order; 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_idnamecuisine
1Biryani BayBiryani
2Kebab KothiBiryani
3Spice RouteBiryani
4Curry LeafBiryani
5Wok ExpressChinese

Orders

order_idrestaurant_idcustomer_idstatus
1181delivered
2181delivered
3182delivered
4183delivered
5281delivered
6282delivered
7282delivered
8284delivered
9385delivered
10386delivered
9 more rows

Output

cuisinenamecustomersrepeat_customersrepeat_rate_pct
BiryaniBiryani Bay3133.3
BiryaniKebab Kothi3133.3
BiryaniSpice Route300
ChineseWok Express4125
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.

Top Two Restaurants per Cuisine by Repeat-Customer Rate

Loyalty is measured per restaurant on delivered orders: a repeat customer has placed at least 2 delivered orders there, and the repeat rate is repeat…

  • Difficulty: Hard
  • Topics: Aggregation, Window Functions, Subqueries
  • Dialect: MySQL
  • Problem: #172

Problem statement

Loyalty is measured per restaurant on delivered orders: a repeat customer has placed at least 2 delivered orders there, and the repeat rate is repeat customers divided by distinct customers. Only restaurants with at least 3 distinct customers are ranked. Within each cuisine, the restaurants with the two highest distinct repeat rates are featured — restaurants with equal rates share a place, so more than two can be featured.

Return cuisine, name, customers, repeat_customers and repeat_rate_pct (the rate as a percentage, rounded to 1 decimal place) for every featured restaurant. Rates are compared exactly, not rounded. Return the rows in any order.

Tables

Table: Restaurant

ColumnType
restaurant_idint
namevarchar
cuisinevarchar

Primary key: restaurant_id.

One row per restaurant.

Table: Orders

ColumnType
order_idint
restaurant_idint
customer_idint
statusenum(delivered, cancelled)

Primary key: order_id.

One row per order; restaurant_id is always in Restaurant.

Examples

Example 1

Restaurant

restaurant_idnamecuisine
1Biryani BayBiryani
2Kebab KothiBiryani
3Spice RouteBiryani
4Curry LeafBiryani
5Wok ExpressChinese

Orders

order_idrestaurant_idcustomer_idstatus
1181delivered
2181delivered
3182delivered
4183delivered
5281delivered
6282delivered
7282delivered
8284delivered
9385delivered
10386delivered
11387delivered
12387cancelled
13481delivered
14481delivered
15582delivered
16583delivered
17584delivered
18584delivered
19585delivered

Output

cuisinenamecustomersrepeat_customersrepeat_rate_pct
BiryaniBiryani Bay3133.3
BiryaniKebab Kothi3133.3
BiryaniSpice Route300
ChineseWok Express4125

How to solve Top Two Restaurants per Cuisine by Repeat-Customer Rate

The metric needs two levels of grouping, so build it in steps. First group delivered orders by restaurant and customer to get each customer's order count there — a cancelled order is filtered out before it can make anyone a customer or a repeat. Then group those rows by restaurant: COUNT(*) is distinct customers, SUM(CASE WHEN n >= 2 …) the repeat customers, and HAVING drops restaurants with fewer than three customers (Curry Leaf has one).

Ranking restarts per cuisine and ties must share a place without using up the next one, which is DENSE_RANK() OVER (PARTITION BY cuisine ORDER BY repeat_customers / customers DESC); keeping place <= 2 features every restaurant on the two highest distinct rates. In the example Biryani Bay and Kebab Kothi tie at 1/3, so they share first place and Spice Route (0) is second. RANK would skip place 2 after the tie and drop it; ROW_NUMBER would break the tie arbitrarily.

The order key is the exact ratio; only the output is rounded. The alternative counts, for each restaurant, how many distinct rates in its cuisine are strictly higher (comparing by cross-multiplication) and keeps it when fewer than two are — the correlated form of a dense rank, quadratic per cuisine.

Reference solution (MySQL)

WITH per_customer AS (
  SELECT restaurant_id, customer_id, COUNT(*) AS n
  FROM Orders
  WHERE status = 'delivered'
  GROUP BY restaurant_id, customer_id
), per_restaurant AS (
  SELECT restaurant_id, COUNT(*) AS customers,
         SUM(CASE WHEN n >= 2 THEN 1 ELSE 0 END) AS repeat_customers
  FROM per_customer
  GROUP BY restaurant_id
  HAVING COUNT(*) >= 3
), ranked AS (
  SELECT r.cuisine, r.name, p.customers, p.repeat_customers,
         DENSE_RANK() OVER (PARTITION BY r.cuisine ORDER BY p.repeat_customers / p.customers DESC) AS place
  FROM per_restaurant p
  JOIN Restaurant r ON r.restaurant_id = p.restaurant_id
)
SELECT cuisine, name, customers, repeat_customers,
       ROUND(100 * repeat_customers / customers, 1) AS repeat_rate_pct
FROM ranked
WHERE place <= 2

Another way

WITH stats AS (
  SELECT o.restaurant_id, r.cuisine, r.name,
         COUNT(DISTINCT o.customer_id) AS customers,
         COUNT(DISTINCT CASE WHEN (SELECT COUNT(*) FROM Orders x WHERE x.restaurant_id = o.restaurant_id
                                   AND x.customer_id = o.customer_id AND x.status = 'delivered') >= 2
                             THEN o.customer_id END) AS repeat_customers
  FROM Orders o JOIN Restaurant r ON r.restaurant_id = o.restaurant_id
  WHERE o.status = 'delivered'
  GROUP BY o.restaurant_id, r.cuisine, r.name
  HAVING COUNT(DISTINCT o.customer_id) >= 3
)
SELECT s.cuisine, s.name, s.customers, s.repeat_customers, ROUND(100 * s.repeat_customers / s.customers, 1) AS repeat_rate_pct
FROM stats s
WHERE (SELECT COUNT(DISTINCT t.repeat_customers / t.customers) FROM stats t
       WHERE t.cuisine = s.cuisine AND t.repeat_customers * s.customers > s.repeat_customers * t.customers) < 2

← Rider Cash-in-Hand Between Deposits · Diamond and Master Players Who Went Quiet →