Ola: Hypercharger Stations Idle Through a Whole Week

Easy
824.

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.

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

ColumnType
station_idint
station_namevarchar
cityvarchar
commissioned_ondate

Primary key: station_id.

One row per fast-charging station.

Table: ChargeSession

ColumnType
session_idint
station_idint
scooter_idint
started_atdatetime
kwhdecimal

Primary key: session_id.

One row per charging session; station_id always names a station.

Example

The rows must come back in the order shown.

Hypercharger

station_idstation_namecitycommissioned_on
1HC KoramangalaBengaluru2024-03-12
2HC BanerPune2024-05-20
3HC GachibowliHyderabad2024-06-03
4HC VelacheryChennai2024-06-05
5HC SaketDelhi2024-04-01

ChargeSession

session_idstation_idscooter_idstarted_atkwh
101190012024-06-04 08:15:002.4
102290022024-06-02 21:40:001.8
103290032024-06-10 00:00:002.1
104590042024-06-09 23:58:303
105390052024-05-30 10:00:001.2

Output

station_idstation_namecity
2HC BanerPune
3HC GachibowliHyderabad
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: 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

ColumnType
station_idint
station_namevarchar
cityvarchar
commissioned_ondate

Primary key: station_id.

One row per fast-charging station.

Table: ChargeSession

ColumnType
session_idint
station_idint
scooter_idint
started_atdatetime
kwhdecimal

Primary key: session_id.

One row per charging session; station_id always names a station.

Examples

Example 1

Hypercharger

station_idstation_namecitycommissioned_on
1HC KoramangalaBengaluru2024-03-12
2HC BanerPune2024-05-20
3HC GachibowliHyderabad2024-06-03
4HC VelacheryChennai2024-06-05
5HC SaketDelhi2024-04-01

ChargeSession

session_idstation_idscooter_idstarted_atkwh
101190012024-06-04 08:15:002.4
102290022024-06-02 21:40:001.8
103290032024-06-10 00:00:002.1
104590042024-06-09 23:58:303
105390052024-05-30 10:00:001.2

Output

station_idstation_namecity
2HC BanerPune
3HC GachibowliHyderabad

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_id

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

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