Peak-Hour Share of Orders by City

Medium
157.

Peak-Hour Share of Orders by City

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

ColumnType
restaurant_idint
namevarchar
cityvarchar

Primary key: restaurant_id.

One row per restaurant.

Table: Orders

ColumnType
order_idint
restaurant_idint
placed_atdatetime
order_valueint

Primary key: order_id.

One row per order; restaurant_id is always in Restaurant.

Example

The rows must come back in the order shown.

Restaurant

restaurant_idnamecity
1Biryani BayHyderabad
2Tandoor TalesHyderabad
3Idli FactoryChennai
4Momo MansionKolkata

Orders

order_idrestaurant_idplaced_atorder_value
112024-08-01 12:00:00300
212024-08-01 14:59:00250
322024-08-01 15:00:00410
422024-08-01 21:30:00380
532024-08-01 08:15:00120
632024-08-01 22:59:00160
732024-08-02 23:00:00140
812024-08-02 19:00:00290

Output

citytotal_orderspeak_orderspeak_share_pct
Hyderabad5480
Chennai3133.33
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.

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

ColumnType
restaurant_idint
namevarchar
cityvarchar

Primary key: restaurant_id.

One row per restaurant.

Table: Orders

ColumnType
order_idint
restaurant_idint
placed_atdatetime
order_valueint

Primary key: order_id.

One row per order; restaurant_id is always in Restaurant.

Examples

Example 1

Restaurant

restaurant_idnamecity
1Biryani BayHyderabad
2Tandoor TalesHyderabad
3Idli FactoryChennai
4Momo MansionKolkata

Orders

order_idrestaurant_idplaced_atorder_value
112024-08-01 12:00:00300
212024-08-01 14:59:00250
322024-08-01 15:00:00410
422024-08-01 21:30:00380
532024-08-01 08:15:00120
632024-08-01 22:59:00160
732024-08-02 23:00:00140
812024-08-02 19:00:00290

Output

citytotal_orderspeak_orderspeak_share_pct
Hyderabad5480
Chennai3133.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.city

Another 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, city

Another 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 →