Peak-Hour Share of Orders by City — SQL Dates Problem
City managers staff riders for two peaks: lunch, 12:00–14:59, and dinner, 19:00–22:59 (by the hour the order was placed).
- Difficulty: Medium
- Topics: Dates, Conditional Logic, Aggregation
- Dialect: MySQL
- Problem: #157
Problem statement
City managers staff riders for two peaks: lunch, 12:00–14:59, and dinner, 19:00–22:59 (by the hour the order was placed). An order placed at 15:00 or 23:00 is off-peak.
For every city that has at least one order, return city, total_orders, peak_orders and peak_share_pct (peak orders as a percentage of all the city's orders, rounded to 2 decimal places). The city is the restaurant's city. Order the rows by peak_share_pct descending, then city ascending.
Tables
Table: Restaurant
| Column | Type |
|---|---|
| restaurant_id | int |
| name | varchar |
| city | varchar |
Primary key: restaurant_id.
One row per restaurant.
Table: Orders
| Column | Type |
|---|---|
| order_id | int |
| restaurant_id | int |
| placed_at | datetime |
| order_value | int |
Primary key: order_id.
One row per order; restaurant_id is always in Restaurant.
Examples
Example 1
Restaurant
| restaurant_id | name | city |
|---|---|---|
| 1 | Biryani Bay | Hyderabad |
| 2 | Tandoor Tales | Hyderabad |
| 3 | Idli Factory | Chennai |
| 4 | Momo Mansion | Kolkata |
Orders
| order_id | restaurant_id | placed_at | order_value |
|---|---|---|---|
| 1 | 1 | 2024-08-01 12:00:00 | 300 |
| 2 | 1 | 2024-08-01 14:59:00 | 250 |
| 3 | 2 | 2024-08-01 15:00:00 | 410 |
| 4 | 2 | 2024-08-01 21:30:00 | 380 |
| 5 | 3 | 2024-08-01 08:15:00 | 120 |
| 6 | 3 | 2024-08-01 22:59:00 | 160 |
| 7 | 3 | 2024-08-02 23:00:00 | 140 |
| 8 | 1 | 2024-08-02 19:00:00 | 290 |
Output
| city | total_orders | peak_orders | peak_share_pct |
|---|---|---|---|
| Hyderabad | 5 | 4 | 80 |
| Chennai | 3 | 1 | 33.33 |
How to solve Peak-Hour Share of Orders by City
The peak windows are whole hours, so the cleanest test is on HOUR(placed_at): lunch is hours 12, 13 and 14 (14:59 included, 15:00 not), dinner hours 19 to 22. Comparing full times (>= '12:00' AND < '15:00') says the same thing but needs the time part extracted first; the hour avoids boundary slips.
Join each order to its restaurant to learn the city, group by city, and count twice in one pass: COUNT(*) for all orders and SUM(CASE WHEN peak THEN 1 ELSE 0 END) for the peak ones. The share is their ratio times 100 — or the average of the 0/1 flag times 100, which is the same number — rounded to two decimals as asked.
Cities without orders never appear because the query starts from orders. Two cities can share a percentage, so city breaks the tie. The cost is one join and one group per city.
Reference solution (MySQL)
SELECT r.city,
COUNT(*) AS total_orders,
SUM(CASE WHEN HOUR(o.placed_at) BETWEEN 12 AND 14 OR HOUR(o.placed_at) BETWEEN 19 AND 22 THEN 1 ELSE 0 END) AS peak_orders,
ROUND(100 * SUM(CASE WHEN HOUR(o.placed_at) BETWEEN 12 AND 14 OR HOUR(o.placed_at) BETWEEN 19 AND 22 THEN 1 ELSE 0 END) / COUNT(*), 2) AS peak_share_pct
FROM Orders o
JOIN Restaurant r ON r.restaurant_id = o.restaurant_id
GROUP BY r.city
ORDER BY peak_share_pct DESC, r.cityAnother way
SELECT city, COUNT(*) AS total_orders, SUM(is_peak) AS peak_orders, ROUND(SUM(is_peak) * 100 / COUNT(*), 2) AS peak_share_pct
FROM (SELECT r.city, IF(DATE_FORMAT(o.placed_at, '%H') IN ('12', '13', '14', '19', '20', '21', '22'), 1, 0) AS is_peak
FROM Orders o JOIN Restaurant r ON r.restaurant_id = o.restaurant_id) t
GROUP BY city
ORDER BY peak_share_pct DESC, cityAnother way
SELECT r.city, COUNT(*) AS total_orders, COUNT(CASE WHEN HOUR(o.placed_at) IN (12, 13, 14, 19, 20, 21, 22) THEN 1 END) AS peak_orders, ROUND(100 * AVG(CASE WHEN HOUR(o.placed_at) IN (12, 13, 14, 19, 20, 21, 22) THEN 1 ELSE 0 END), 2) AS peak_share_pct FROM Orders o JOIN Restaurant r ON r.restaurant_id = o.restaurant_id GROUP BY r.city ORDER BY 4 DESC, 1← Riders Missing the Delivery SLA in June 2024 · Best-Selling Dish of Each Restaurant →