Ola: Each Scooter's Opening and Closing Readings per Day
The connected-vehicle team builds a daily sheet for every scooter from its raw telemetry: where the odometer and the battery stood at the first reading of…
- Difficulty: Hard
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #842
Problem statement
The connected-vehicle team builds a daily sheet for every scooter from its raw telemetry: where the odometer and the battery stood at the first reading of the day and at the last.
For each scooter and calendar day (the date part of read_at; a reading at 00:00:00 belongs to the new day), the opening reading is the day's earliest and the closing reading is the day's latest. Return scooter_id, ride_date, opening_km, closing_km, km_ridden (closing minus opening odometer), opening_soc and closing_soc (battery percent at those two readings) — only for days on which the scooter moved (km_ridden above 0). A day with a single reading never qualifies. Order the rows by scooter_id, then ride_date.
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: Telemetry
| Column | Type |
|---|---|
| scooter_id | int |
| read_at | datetime |
| odometer_km | int |
| soc_pct | int |
Primary key: scooter_id, read_at.
One row per telemetry reading. The odometer never goes down; the state of charge rises when the scooter is charged.
Examples
Example 1
Telemetry
| scooter_id | read_at | odometer_km | soc_pct |
|---|---|---|---|
| 801 | 2024-11-04 07:55:00 | 1520 | 92 |
| 801 | 2024-11-04 09:10:00 | 1534 | 81 |
| 801 | 2024-11-04 21:40:00 | 1561 | 58 |
| 801 | 2024-11-05 00:00:00 | 1561 | 57 |
| 801 | 2024-11-05 18:30:00 | 1561 | 96 |
| 801 | 2024-11-06 08:00:00 | 1561 | 96 |
| 801 | 2024-11-06 08:45:00 | 1575 | 88 |
| 802 | 2024-11-04 23:59:59 | 300 | 40 |
| 802 | 2024-11-05 06:00:00 | 302 | 39 |
| 802 | 2024-11-05 19:15:00 | 331 | 12 |
Output
| scooter_id | ride_date | opening_km | closing_km | km_ridden | opening_soc | closing_soc |
|---|---|---|---|---|---|---|
| 801 | 2024-11-04 | 1520 | 1561 | 41 | 92 | 58 |
| 801 | 2024-11-06 | 1561 | 1575 | 14 | 96 | 88 |
| 802 | 2024-11-05 | 302 | 331 | 29 | 39 | 12 |
How to solve Ola: Each Scooter's Opening and Closing Readings per Day
Opening and closing values are the classic use of FIRST_VALUE and LAST_VALUE. Partition the readings by scooter and by the reading's date (CAST(read_at AS DATE), so midnight starts a new day) and order them by read_at; FIRST_VALUE(odometer_km) is then the opening reading on every row of the day, and the same window with soc_pct gives the opening charge.
The trap is the frame. With an ORDER BY, a window's default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so LAST_VALUE returns the current row's own value — the "last so far" — and the closing reading would differ on every row. Declaring ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING makes every row see the whole day. Because a window keeps one output row per reading, DISTINCT collapses each scooter-day to one row (all its values are identical across the day), and the km_ridden > 0 filter drops parked days and single-reading days, whose opening and closing are the same reading.
The first alternative finds each day's first and last timestamps with MIN/MAX and joins the two readings back by the primary key. The second numbers the day's readings in both directions with ROW_NUMBER and pairs row 1 up with row 1 down. All three sort each scooter's readings once.
Reference solution (MySQL)
WITH day_view AS (
SELECT scooter_id, CAST(read_at AS DATE) AS ride_date,
FIRST_VALUE(odometer_km) OVER (PARTITION BY scooter_id, CAST(read_at AS DATE) ORDER BY read_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS opening_km,
LAST_VALUE(odometer_km) OVER (PARTITION BY scooter_id, CAST(read_at AS DATE) ORDER BY read_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS closing_km,
FIRST_VALUE(soc_pct) OVER (PARTITION BY scooter_id, CAST(read_at AS DATE) ORDER BY read_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS opening_soc,
LAST_VALUE(soc_pct) OVER (PARTITION BY scooter_id, CAST(read_at AS DATE) ORDER BY read_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS closing_soc
FROM Telemetry
)
SELECT DISTINCT scooter_id, ride_date, opening_km, closing_km, closing_km - opening_km AS km_ridden, opening_soc, closing_soc
FROM day_view
WHERE closing_km - opening_km > 0
ORDER BY scooter_id, ride_dateAnother way
WITH bounds AS (
SELECT scooter_id, CAST(read_at AS DATE) AS ride_date, MIN(read_at) AS first_at, MAX(read_at) AS last_at
FROM Telemetry
GROUP BY scooter_id, CAST(read_at AS DATE)
)
SELECT b.scooter_id, b.ride_date, o.odometer_km AS opening_km, c.odometer_km AS closing_km,
c.odometer_km - o.odometer_km AS km_ridden, o.soc_pct AS opening_soc, c.soc_pct AS closing_soc
FROM bounds b
JOIN Telemetry o ON o.scooter_id = b.scooter_id AND o.read_at = b.first_at
JOIN Telemetry c ON c.scooter_id = b.scooter_id AND c.read_at = b.last_at
WHERE c.odometer_km > o.odometer_km
ORDER BY b.scooter_id, b.ride_dateAnother way
WITH r AS (
SELECT scooter_id, CAST(read_at AS DATE) AS ride_date, odometer_km, soc_pct,
ROW_NUMBER() OVER (PARTITION BY scooter_id, CAST(read_at AS DATE) ORDER BY read_at) AS up,
ROW_NUMBER() OVER (PARTITION BY scooter_id, CAST(read_at AS DATE) ORDER BY read_at DESC) AS down
FROM Telemetry
)
SELECT o.scooter_id, o.ride_date, o.odometer_km AS opening_km, c.odometer_km AS closing_km,
c.odometer_km - o.odometer_km AS km_ridden, o.soc_pct AS opening_soc, c.soc_pct AS closing_soc
FROM r o JOIN r c ON c.scooter_id = o.scooter_id AND c.ride_date = o.ride_date AND c.down = 1
WHERE o.up = 1 AND c.odometer_km > o.odometer_km
ORDER BY o.scooter_id, o.ride_date← Ola: Scooters Behind Their Model's Newest Firmware · Ola: Driver-Partner Activation and Retention by Approval Month →