Peak Order Times
ASKED IN INTERVIEWZomato
Problem Statement
Zomato operations team wants to optimize delivery partner allocation by identifying peak demand periods. For each city, find the 1-hour window (e.g., 13:00 to 14:00) that has the highest number of orders.
Write an SQL query to determine the peak hour for every city in the dataset.
Rules:
- The peak hour is defined as the starting hour of the 60-minute window (e.g., if the hour is 18, it represents the window 18:00:00 to 18:59:59).
- If there is a tie for the peak hour in a city, return the earliest hour.
- Output columns: city_id, peak_hour, order_count.
- Results should be ordered by city_id in ascending order.
Table Schema:
- Cities: Mapping of city IDs to city names.
- Orders: Detailed records of every order placed on the platform.
Tests your understanding of
Window Functions, Aggregation, DateTime and Ranking
Input Tables
Cities
| city_id(INTEGER) | city_name(VARCHAR) |
|---|---|
| 1 | Mumbai |
| 2 | Bangalore |
| 3 | Delhi |
Orders
| order_id(INTEGER) | city_id(INTEGER) | order_timestamp(TIMESTAMP) |
|---|---|---|
| 101 | 1 | 2026-02-10 13:05:00 |
| 102 | 1 | 2026-02-10 13:25:00 |
| 103 | 1 | 2026-02-10 14:10:00 |
| 104 | 2 | 2026-02-10 19:00:00 |
| 105 | 2 | 2026-02-10 19:45:00 |
| 106 | 2 | 2026-02-10 20:15:00 |
| 107 | 1 | 2026-02-10 13:55:00 |
Expected Output
| city_id(INTEGER) | peak_hour(INTEGER) | order_count(INTEGER) |
|---|---|---|
| 1 | 13 | 3 |
| 2 | 19 | 2 |
Tags
MediumASKED IN INTERVIEWWindow FunctionsAggregationDateTimeRanking
20-25 min
45%