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
| Column | Type |
|---|---|
| restaurant_id | int |
| name | varchar |
| cuisine | varchar |
Primary key: restaurant_id.
One row per restaurant.
Table: Orders
| Column | Type |
|---|---|
| order_id | int |
| restaurant_id | int |
| customer_id | int |
| status | enum(delivered, cancelled) |
Primary key: order_id.
One row per order; restaurant_id is always in Restaurant.
Examples
Example 1
Restaurant
| restaurant_id | name | cuisine |
|---|---|---|
| 1 | Biryani Bay | Biryani |
| 2 | Kebab Kothi | Biryani |
| 3 | Spice Route | Biryani |
| 4 | Curry Leaf | Biryani |
| 5 | Wok Express | Chinese |
Orders
| order_id | restaurant_id | customer_id | status |
|---|---|---|---|
| 1 | 1 | 81 | delivered |
| 2 | 1 | 81 | delivered |
| 3 | 1 | 82 | delivered |
| 4 | 1 | 83 | delivered |
| 5 | 2 | 81 | delivered |
| 6 | 2 | 82 | delivered |
| 7 | 2 | 82 | delivered |
| 8 | 2 | 84 | delivered |
| 9 | 3 | 85 | delivered |
| 10 | 3 | 86 | delivered |
| 11 | 3 | 87 | delivered |
| 12 | 3 | 87 | cancelled |
| 13 | 4 | 81 | delivered |
| 14 | 4 | 81 | delivered |
| 15 | 5 | 82 | delivered |
| 16 | 5 | 83 | delivered |
| 17 | 5 | 84 | delivered |
| 18 | 5 | 84 | delivered |
| 19 | 5 | 85 | delivered |
Output
| cuisine | name | customers | repeat_customers | repeat_rate_pct |
|---|---|---|---|---|
| Biryani | Biryani Bay | 3 | 1 | 33.3 |
| Biryani | Kebab Kothi | 3 | 1 | 33.3 |
| Biryani | Spice Route | 3 | 0 | 0 |
| Chinese | Wok Express | 4 | 1 | 25 |
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 <= 2Another 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 →