Ola: First Fault Code After Each Software Install

Hard
846.

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.

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

ColumnType
install_idint
scooter_idint
versionvarchar
installed_atdatetime

Primary key: install_id.

One row per software install; a scooter never has two installs at the same second.

Table: FaultEvent

ColumnType
fault_idint
scooter_idint
fault_codevarchar
raised_atdatetime

Primary key: fault_id.

One row per diagnostic fault a scooter raised.

Example

The rows must come back in the order shown.

SoftwareInstall

install_idscooter_idversioninstalled_at
14014.0.02024-09-03 02:00:00
24014.0.12024-09-10 02:00:00
34024.0.02024-09-04 03:00:00
44034.0.12024-09-11 01:30:00
54024.0.12024-09-12 03:00:00

FaultEvent

fault_idscooter_idfault_coderaised_at
1401BMS-122024-09-02 23:00:00
2401MC-032024-09-05 10:15:00
3401BMS-122024-09-06 08:00:00
4401DSP-012024-09-10 02:00:00
5401MC-032024-09-10 05:30:00
6402TPMS-022024-09-13 09:00:00
7402BMS-122024-09-13 09:00:00

Output

install_idscooter_idversionfirst_fault_codeminutes_to_fault
14014.0.0MC-033375
24014.0.1MC-03210
34024.0.0NULLNULL
44034.0.1NULLNULL
54024.0.1TPMS-021800
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: 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

ColumnType
install_idint
scooter_idint
versionvarchar
installed_atdatetime

Primary key: install_id.

One row per software install; a scooter never has two installs at the same second.

Table: FaultEvent

ColumnType
fault_idint
scooter_idint
fault_codevarchar
raised_atdatetime

Primary key: fault_id.

One row per diagnostic fault a scooter raised.

Examples

Example 1

SoftwareInstall

install_idscooter_idversioninstalled_at
14014.0.02024-09-03 02:00:00
24014.0.12024-09-10 02:00:00
34024.0.02024-09-04 03:00:00
44034.0.12024-09-11 01:30:00
54024.0.12024-09-12 03:00:00

FaultEvent

fault_idscooter_idfault_coderaised_at
1401BMS-122024-09-02 23:00:00
2401MC-032024-09-05 10:15:00
3401BMS-122024-09-06 08:00:00
4401DSP-012024-09-10 02:00:00
5401MC-032024-09-10 05:30:00
6402TPMS-022024-09-13 09:00:00
7402BMS-122024-09-13 09:00:00

Output

install_idscooter_idversionfirst_fault_codeminutes_to_fault
14014.0.0MC-033375
24014.0.1MC-03210
34024.0.0NULLNULL
44034.0.1NULLNULL
54024.0.1TPMS-021800

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_id

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