Ola: Busiest Hour of the Day at Each Hypercharger
To schedule technicians outside the rush, the charging-network team needs each station's busiest hour of the day.
- Difficulty: Medium
- Topics: Window Functions, Dates, Aggregation
- Dialect: MySQL
- Problem: #832
Problem statement
To schedule technicians outside the rush, the charging-network team needs each station's busiest hour of the day.
For every station with at least one session, find the hour of the day (0 to 23, from started_at) in which the most sessions started, over all dates together. If several hours tie, take the earliest hour. Return station_name, busiest_hour and sessions (the number of sessions that started in that hour), ordered by station_name.
A practice scenario set at Ola: the tables and data are invented, and this is not a question from, or endorsed by, Ola.
Tables
Table: Hypercharger
| Column | Type |
|---|---|
| station_id | int |
| station_name | varchar |
| city | varchar |
Primary key: station_id.
One row per station; names are unique.
Table: ChargeSession
| Column | Type |
|---|---|
| session_id | int |
| station_id | int |
| started_at | datetime |
| kwh | decimal |
Primary key: session_id.
One row per session; station_id always names a station.
Examples
Example 1
Hypercharger
| station_id | station_name | city |
|---|---|---|
| 1 | HC Koramangala | Bengaluru |
| 2 | HC Baner | Pune |
| 3 | HC Saket | Delhi |
ChargeSession
| session_id | station_id | started_at | kwh |
|---|---|---|---|
| 1 | 1 | 2024-07-01 08:05:00 | 2 |
| 2 | 1 | 2024-07-01 08:40:00 | 1.5 |
| 3 | 1 | 2024-07-02 19:10:00 | 2.2 |
| 4 | 1 | 2024-07-03 08:59:59 | 1.1 |
| 5 | 2 | 2024-07-01 19:00:00 | 3 |
| 6 | 2 | 2024-07-02 09:30:00 | 2.4 |
| 7 | 2 | 2024-07-03 19:45:00 | 1.9 |
| 8 | 2 | 2024-07-04 09:05:00 | 2.6 |
Output
| station_name | busiest_hour | sessions |
|---|---|---|
| HC Baner | 9 | 2 |
| HC Koramangala | 8 | 3 |
How to solve Ola: Busiest Hour of the Day at Each Hypercharger
This is a "top one per group" question on top of an aggregation. The first step folds all dates together and counts sessions per station and hour of the day: HOUR(started_at) extracts 0–23, and grouping by it gives one row per station-hour.
The second step picks the best hour inside each station. ROW_NUMBER() OVER (PARTITION BY station_id ORDER BY sessions DESC, hr) numbers the hours from busiest to quietest, and among hours with equal counts the earlier one comes first, so row 1 is exactly the requested hour. A station with no sessions has no rows in ChargeSession, so it never appears. The station name is joined on at the end.
The alternative keeps an hour when no other hour of the same station beats it — more sessions, or the same number at an earlier hour — using NOT EXISTS over the same CTE. It needs no window function but compares every pair of hours within a station, which is still tiny here (at most 24 per station).
Reference solution (MySQL)
WITH per_hour AS (
SELECT station_id, HOUR(started_at) AS hr, COUNT(*) AS sessions
FROM ChargeSession
GROUP BY station_id, HOUR(started_at)
), ranked AS (
SELECT station_id, hr, sessions,
ROW_NUMBER() OVER (PARTITION BY station_id ORDER BY sessions DESC, hr) AS rn
FROM per_hour
)
SELECT h.station_name, r.hr AS busiest_hour, r.sessions
FROM ranked r
JOIN Hypercharger h ON h.station_id = r.station_id
WHERE r.rn = 1
ORDER BY h.station_nameAnother way
WITH per_hour AS (SELECT station_id, HOUR(started_at) AS hr, COUNT(*) AS sessions FROM ChargeSession GROUP BY station_id, HOUR(started_at))
SELECT h.station_name, p.hr AS busiest_hour, p.sessions
FROM per_hour p JOIN Hypercharger h ON h.station_id = p.station_id
WHERE NOT EXISTS (SELECT 1 FROM per_hour q WHERE q.station_id = p.station_id AND (q.sessions > p.sessions OR (q.sessions = p.sessions AND q.hr < p.hr)))
ORDER BY h.station_name← Ola: Scooters With Outlier Energy Use Within Their Model · Ola: Test Riders Who Bought Within 30 Days, per Centre →