Merged Alarm Downtime per Machine

Hard
268.

Merged Alarm Downtime per Machine

Several subsystems of a machine (hydraulics, coolant, safety door…) raise alarms independently, and a machine is down whenever at least one alarm is active. Alarms often overlap, so adding up their lengths double-counts. An alarm that starts exactly when another one ends continues the same outage.

For every machine with at least one alarm, return machine_id, outages — the number of separate down periods after merging overlapping or touching alarms — and down_minutes, the total length of those merged periods in minutes. Order by machine_id.

Tables

Table: MachineAlarm

ColumnType
alarm_idint
machine_idint
subsystemvarchar
alarm_startdatetime
alarm_enddatetime

Primary key: alarm_id.

Times are whole minutes, and alarm_end is always later than alarm_start.

Example

The rows must come back in the order shown.

MachineAlarm

alarm_idmachine_idsubsystemalarm_startalarm_end
11hydraulics2025-06-02 08:00:002025-06-02 08:30:00
21coolant2025-06-02 08:20:002025-06-02 08:50:00
31safety_door2025-06-02 08:25:002025-06-02 08:35:00
41coolant2025-06-02 08:50:002025-06-02 09:00:00
51hydraulics2025-06-02 10:00:002025-06-02 10:15:00
62spindle2025-06-02 09:00:002025-06-02 09:40:00
72coolant2025-06-02 09:05:002025-06-02 09:10:00

Output

machine_idoutagesdown_minutes
1275
2140
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.

Merged Alarm Downtime per Machine — SQL Problem

Several subsystems of a machine (hydraulics, coolant, safety door…) raise alarms independently, and a machine is down whenever at least one alarm is active.

  • Difficulty: Hard
  • Topics: Window Functions, Dates
  • Dialect: MySQL
  • Problem: #268

Problem statement

Several subsystems of a machine (hydraulics, coolant, safety door…) raise alarms independently, and a machine is down whenever at least one alarm is active. Alarms often overlap, so adding up their lengths double-counts. An alarm that starts exactly when another one ends continues the same outage.

For every machine with at least one alarm, return machine_id, outages — the number of separate down periods after merging overlapping or touching alarms — and down_minutes, the total length of those merged periods in minutes. Order by machine_id.

Tables

Table: MachineAlarm

ColumnType
alarm_idint
machine_idint
subsystemvarchar
alarm_startdatetime
alarm_enddatetime

Primary key: alarm_id.

Times are whole minutes, and alarm_end is always later than alarm_start.

Examples

Example 1

MachineAlarm

alarm_idmachine_idsubsystemalarm_startalarm_end
11hydraulics2025-06-02 08:00:002025-06-02 08:30:00
21coolant2025-06-02 08:20:002025-06-02 08:50:00
31safety_door2025-06-02 08:25:002025-06-02 08:35:00
41coolant2025-06-02 08:50:002025-06-02 09:00:00
51hydraulics2025-06-02 10:00:002025-06-02 10:15:00
62spindle2025-06-02 09:00:002025-06-02 09:40:00
72coolant2025-06-02 09:05:002025-06-02 09:10:00

Output

machine_idoutagesdown_minutes
1275
2140

How to solve Merged Alarm Downtime per Machine

This is merging overlapping intervals. Order each machine's alarms by alarm_start (with alarm_id to break ties deterministically). An alarm starts a new outage exactly when it begins after every earlier alarm has ended — that is, after the maximum end seen so far, MAX(alarm_end) OVER (… ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING). Using only LAG(alarm_end) fails when a long alarm swallows a short one and the next alarm still overlaps the long one.

Touching alarms (start equal to the running end) are not a new outage, so the test is strictly alarm_start > reach; the first alarm has no earlier one (reach NULL) and always starts one. A running SUM of those 0/1 flags gives every alarm its outage number, and grouping by it yields each merged period from MIN(alarm_start) to MAX(alarm_end). The machine's answer is the count of periods and the sum of their TIMESTAMPDIFF minutes.

The alternative decides "starts an outage" with NOT EXISTS: no earlier alarm (or same start with smaller id) is still running at this start. That is correct because, in start order, an alarm extends an outage iff some earlier alarm ends at or after its start. It is quadratic per machine; the window version is one sort.

Reference solution (MySQL)

WITH ordered AS (
  SELECT machine_id, alarm_id, alarm_start, alarm_end,
         MAX(alarm_end) OVER (PARTITION BY machine_id ORDER BY alarm_start, alarm_id
                              ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS reach
  FROM MachineAlarm
), marked AS (
  SELECT machine_id, alarm_id, alarm_start, alarm_end,
         SUM(CASE WHEN reach IS NULL OR alarm_start > reach THEN 1 ELSE 0 END)
           OVER (PARTITION BY machine_id ORDER BY alarm_start, alarm_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS outage_no
  FROM ordered
), outages AS (
  SELECT machine_id, outage_no, MIN(alarm_start) AS s, MAX(alarm_end) AS e
  FROM marked GROUP BY machine_id, outage_no
)
SELECT machine_id, COUNT(*) AS outages, SUM(TIMESTAMPDIFF(MINUTE, s, e)) AS down_minutes
FROM outages
GROUP BY machine_id
ORDER BY machine_id

Another way

WITH starts AS (
  SELECT a.machine_id, a.alarm_id, a.alarm_start, a.alarm_end,
         CASE WHEN EXISTS (SELECT 1 FROM MachineAlarm b
                           WHERE b.machine_id = a.machine_id AND b.alarm_end >= a.alarm_start
                             AND (b.alarm_start < a.alarm_start OR (b.alarm_start = a.alarm_start AND b.alarm_id < a.alarm_id)))
              THEN 0 ELSE 1 END AS is_start
  FROM MachineAlarm a
), numbered AS (
  SELECT machine_id, alarm_start, alarm_end,
         SUM(is_start) OVER (PARTITION BY machine_id ORDER BY alarm_start, alarm_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS k
  FROM starts
)
SELECT machine_id, COUNT(*) AS outages, SUM(mins) AS down_minutes FROM (
  SELECT machine_id, k, TIMESTAMPDIFF(MINUTE, MIN(alarm_start), MAX(alarm_end)) AS mins FROM numbered GROUP BY machine_id, k
) t GROUP BY machine_id ORDER BY machine_id

← Longest Defect-Free Batch Streak per Line · Sustained Vibration Breach Episodes →