Ola: Hypercharger Stations Idle Through a Whole Week
The charging-network team reviews fast-charging stations that nobody used, to check for a broken charger or a blocked parking bay.
- Difficulty: Easy
- Topics: Joins, Subqueries
- Dialect: MySQL
- Problem: #824
Problem statement
The charging-network team reviews fast-charging stations that nobody used, to check for a broken charger or a blocked parking bay.
Return every station that was commissioned on or before 2024-06-03 and had no charging session start between 2024-06-03 00:00:00 and 2024-06-09 23:59:59 (the week from Monday to Sunday). Sessions outside that week do not matter, and a station commissioned later in the week is not expected to have traffic yet. Return station_id, station_name and city, ordered by station_id.
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 |
| commissioned_on | date |
Primary key: station_id.
One row per fast-charging station.
Table: ChargeSession
| Column | Type |
|---|---|
| session_id | int |
| station_id | int |
| scooter_id | int |
| started_at | datetime |
| kwh | decimal |
Primary key: session_id.
One row per charging session; station_id always names a station.
Examples
Example 1
Hypercharger
| station_id | station_name | city | commissioned_on |
|---|---|---|---|
| 1 | HC Koramangala | Bengaluru | 2024-03-12 |
| 2 | HC Baner | Pune | 2024-05-20 |
| 3 | HC Gachibowli | Hyderabad | 2024-06-03 |
| 4 | HC Velachery | Chennai | 2024-06-05 |
| 5 | HC Saket | Delhi | 2024-04-01 |
ChargeSession
| session_id | station_id | scooter_id | started_at | kwh |
|---|---|---|---|---|
| 101 | 1 | 9001 | 2024-06-04 08:15:00 | 2.4 |
| 102 | 2 | 9002 | 2024-06-02 21:40:00 | 1.8 |
| 103 | 2 | 9003 | 2024-06-10 00:00:00 | 2.1 |
| 104 | 5 | 9004 | 2024-06-09 23:58:30 | 3 |
| 105 | 3 | 9005 | 2024-05-30 10:00:00 | 1.2 |
Output
| station_id | station_name | city |
|---|---|---|
| 2 | HC Baner | Pune |
| 3 | HC Gachibowli | Hyderabad |
How to solve Ola: Hypercharger Stations Idle Through a Whole Week
The question is about the absence of rows, so it is an anti join. For each station commissioned in time, look for a session at that station that started inside the week; keep the station only when that lookup finds nothing. NOT EXISTS says exactly that.
The week's boundaries have to sit inside the subquery (or, in the join version, inside ON). If the date filter were applied in the outer WHERE after a left join, a station with a session only in May would join that row, survive the IS NULL test incorrectly or be dropped wrongly depending on where the filter sits. A half-open interval — on or after 2024-06-03 and before 2024-06-10 — includes a session at 23:58 on Sunday and excludes one at midnight on Monday.
The LEFT JOIN … IS NULL and NOT IN alternatives give the same result here because station_id is never NULL in ChargeSession; with nullable keys NOT IN would return nothing, which is why NOT EXISTS is the safer habit. With an index on (station_id, started_at) each lookup is one index probe.
Reference solution (MySQL)
SELECT h.station_id, h.station_name, h.city
FROM Hypercharger h
WHERE h.commissioned_on <= '2024-06-03'
AND NOT EXISTS (
SELECT 1 FROM ChargeSession s
WHERE s.station_id = h.station_id
AND s.started_at >= '2024-06-03' AND s.started_at < '2024-06-10'
)
ORDER BY h.station_idAnother way
SELECT h.station_id, h.station_name, h.city
FROM Hypercharger h
LEFT JOIN ChargeSession s ON s.station_id = h.station_id AND s.started_at BETWEEN '2024-06-03 00:00:00' AND '2024-06-09 23:59:59'
WHERE h.commissioned_on <= '2024-06-03' AND s.session_id IS NULL
ORDER BY h.station_idAnother way
SELECT station_id, station_name, city FROM Hypercharger WHERE commissioned_on <= '2024-06-03' AND station_id NOT IN (SELECT station_id FROM ChargeSession WHERE CAST(started_at AS DATE) BETWEEN '2024-06-03' AND '2024-06-09') ORDER BY station_id← Ola: Scooters Still Below MoveOS 4.2 · Ola: Completed Rentals Bookings per Hourly Package →