Ola: Each Scooter's Opening and Closing Readings per Day

Hard
842.

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

ColumnType
scooter_idint
read_atdatetime
odometer_kmint
soc_pctint

Primary key (together): scooter_id, read_at.

One row per telemetry reading. The odometer never goes down; the state of charge rises when the scooter is charged.

Example

The rows must come back in the order shown.

Telemetry

scooter_idread_atodometer_kmsoc_pct
8012024-11-04 07:55:00152092
8012024-11-04 09:10:00153481
8012024-11-04 21:40:00156158
8012024-11-05 00:00:00156157
8012024-11-05 18:30:00156196
8012024-11-06 08:00:00156196
8012024-11-06 08:45:00157588
8022024-11-04 23:59:5930040
8022024-11-05 06:00:0030239
8022024-11-05 19:15:0033112

Output

scooter_idride_dateopening_kmclosing_kmkm_riddenopening_socclosing_soc
8012024-11-0415201561419258
8012024-11-0615611575149688
8022024-11-05302331293912
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: 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

ColumnType
scooter_idint
read_atdatetime
odometer_kmint
soc_pctint

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_idread_atodometer_kmsoc_pct
8012024-11-04 07:55:00152092
8012024-11-04 09:10:00153481
8012024-11-04 21:40:00156158
8012024-11-05 00:00:00156157
8012024-11-05 18:30:00156196
8012024-11-06 08:00:00156196
8012024-11-06 08:45:00157588
8022024-11-04 23:59:5930040
8022024-11-05 06:00:0030239
8022024-11-05 19:15:0033112

Output

scooter_idride_dateopening_kmclosing_kmkm_riddenopening_socclosing_soc
8012024-11-0415201561419258
8012024-11-0615611575149688
8022024-11-05302331293912

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_date

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

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