Ola: Busiest Hour of the Day at Each Hypercharger

Medium
832.

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.

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

ColumnType
station_idint
station_namevarchar
cityvarchar

Primary key: station_id.

One row per station; names are unique.

Table: ChargeSession

ColumnType
session_idint
station_idint
started_atdatetime
kwhdecimal

Primary key: session_id.

One row per session; station_id always names a station.

Example

The rows must come back in the order shown.

Hypercharger

station_idstation_namecity
1HC KoramangalaBengaluru
2HC BanerPune
3HC SaketDelhi

ChargeSession

session_idstation_idstarted_atkwh
112024-07-01 08:05:002
212024-07-01 08:40:001.5
312024-07-02 19:10:002.2
412024-07-03 08:59:591.1
522024-07-01 19:00:003
622024-07-02 09:30:002.4
722024-07-03 19:45:001.9
822024-07-04 09:05:002.6

Output

station_namebusiest_hoursessions
HC Baner92
HC Koramangala83
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.

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

ColumnType
station_idint
station_namevarchar
cityvarchar

Primary key: station_id.

One row per station; names are unique.

Table: ChargeSession

ColumnType
session_idint
station_idint
started_atdatetime
kwhdecimal

Primary key: session_id.

One row per session; station_id always names a station.

Examples

Example 1

Hypercharger

station_idstation_namecity
1HC KoramangalaBengaluru
2HC BanerPune
3HC SaketDelhi

ChargeSession

session_idstation_idstarted_atkwh
112024-07-01 08:05:002
212024-07-01 08:40:001.5
312024-07-02 19:10:002.2
412024-07-03 08:59:591.1
522024-07-01 19:00:003
622024-07-02 09:30:002.4
722024-07-03 19:45:001.9
822024-07-04 09:05:002.6

Output

station_namebusiest_hoursessions
HC Baner92
HC Koramangala83

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_name

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