Ola: First Fault Code After Each Software Install
Before widening a rollout, the software-quality team checks how soon scooters raise a fault after taking an update — and which fault comes first.
- Difficulty: Hard
- Topics: Window Functions, Joins, Dates
- Dialect: MySQL
- Problem: #846
Problem statement
Before widening a rollout, the software-quality team checks how soon scooters raise a fault after taking an update — and which fault comes first.
For every install, find the first fault the same scooter raised strictly after the install and strictly before that scooter's next install (if it has one); a fault at the exact second of the next install belongs to neither. If two faults share the earliest second, take the one with the smaller fault_id. Return install_id, scooter_id, version, first_fault_code and minutes_to_fault (whole minutes from the install to that fault), with NULL in the last two when no fault falls in the window. Order the rows by install_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: SoftwareInstall
| Column | Type |
|---|---|
| install_id | int |
| scooter_id | int |
| version | varchar |
| installed_at | datetime |
Primary key: install_id.
One row per software install; a scooter never has two installs at the same second.
Table: FaultEvent
| Column | Type |
|---|---|
| fault_id | int |
| scooter_id | int |
| fault_code | varchar |
| raised_at | datetime |
Primary key: fault_id.
One row per diagnostic fault a scooter raised.
Examples
Example 1
SoftwareInstall
| install_id | scooter_id | version | installed_at |
|---|---|---|---|
| 1 | 401 | 4.0.0 | 2024-09-03 02:00:00 |
| 2 | 401 | 4.0.1 | 2024-09-10 02:00:00 |
| 3 | 402 | 4.0.0 | 2024-09-04 03:00:00 |
| 4 | 403 | 4.0.1 | 2024-09-11 01:30:00 |
| 5 | 402 | 4.0.1 | 2024-09-12 03:00:00 |
FaultEvent
| fault_id | scooter_id | fault_code | raised_at |
|---|---|---|---|
| 1 | 401 | BMS-12 | 2024-09-02 23:00:00 |
| 2 | 401 | MC-03 | 2024-09-05 10:15:00 |
| 3 | 401 | BMS-12 | 2024-09-06 08:00:00 |
| 4 | 401 | DSP-01 | 2024-09-10 02:00:00 |
| 5 | 401 | MC-03 | 2024-09-10 05:30:00 |
| 6 | 402 | TPMS-02 | 2024-09-13 09:00:00 |
| 7 | 402 | BMS-12 | 2024-09-13 09:00:00 |
Output
| install_id | scooter_id | version | first_fault_code | minutes_to_fault |
|---|---|---|---|---|
| 1 | 401 | 4.0.0 | MC-03 | 3375 |
| 2 | 401 | 4.0.1 | MC-03 | 210 |
| 3 | 402 | 4.0.0 | NULL | NULL |
| 4 | 403 | 4.0.1 | NULL | NULL |
| 5 | 402 | 4.0.1 | TPMS-02 | 1800 |
How to solve Ola: First Fault Code After Each Software Install
The question attaches events of one stream (faults) to intervals defined by another (installs). Each install's interval runs from its own installed_at to the scooter's next install, which LEAD(installed_at) over the scooter's installs provides; the last install has a NULL end and its interval is open.
Faults are joined to the interval that contains them — strictly after the install and, when there is a next install, strictly before it. Strict comparisons at both ends mean a fault at the exact second of an install falls in no interval, as the statement asks. Among the faults of one interval, ROW_NUMBER() ordered by raised_at and then fault_id marks the first, settling same-second ties on the smaller id. A final LEFT JOIN of the first hit onto every install keeps installs with no fault, whose code and minutes come out NULL; TIMESTAMPDIFF(MINUTE, …) gives whole minutes.
The alternative finds the first fault per install with a correlated ORDER BY … LIMIT 1 subquery, expressing "before the next install" as "no install of the scooter lies between this install and the fault". It costs a sorted lookup per install instead of one windowed join.
Reference solution (MySQL)
WITH windowed AS (
SELECT install_id, scooter_id, version, installed_at,
LEAD(installed_at) OVER (PARTITION BY scooter_id ORDER BY installed_at) AS next_at
FROM SoftwareInstall
), hits AS (
SELECT w.install_id, f.fault_code, f.raised_at,
ROW_NUMBER() OVER (PARTITION BY w.install_id ORDER BY f.raised_at, f.fault_id) AS rn
FROM windowed w
JOIN FaultEvent f ON f.scooter_id = w.scooter_id
AND f.raised_at > w.installed_at
AND (w.next_at IS NULL OR f.raised_at < w.next_at)
)
SELECT w.install_id, w.scooter_id, w.version,
h.fault_code AS first_fault_code,
TIMESTAMPDIFF(MINUTE, w.installed_at, h.raised_at) AS minutes_to_fault
FROM windowed w
LEFT JOIN hits h ON h.install_id = w.install_id AND h.rn = 1
ORDER BY w.install_idAnother way
WITH firsts AS (
SELECT i.install_id, i.scooter_id, i.version, i.installed_at,
(SELECT f.fault_id FROM FaultEvent f
WHERE f.scooter_id = i.scooter_id AND f.raised_at > i.installed_at
AND NOT EXISTS (SELECT 1 FROM SoftwareInstall n WHERE n.scooter_id = i.scooter_id
AND n.installed_at > i.installed_at AND n.installed_at <= f.raised_at)
ORDER BY f.raised_at, f.fault_id LIMIT 1) AS fault_id
FROM SoftwareInstall i
)
SELECT x.install_id, x.scooter_id, x.version, f.fault_code AS first_fault_code,
TIMESTAMPDIFF(MINUTE, x.installed_at, f.raised_at) AS minutes_to_fault
FROM firsts x
LEFT JOIN FaultEvent f ON f.fault_id = x.fault_id
ORDER BY x.install_id← Ola: Peak Simultaneous Charging Sessions at Each Station · Ola: Service Tickets Breaching the 3-Working-Day SLA →