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
| Column | Type |
|---|---|
| alarm_id | int |
| machine_id | int |
| subsystem | varchar |
| alarm_start | datetime |
| alarm_end | datetime |
Primary key: alarm_id.
Times are whole minutes, and alarm_end is always later than alarm_start.
Examples
Example 1
MachineAlarm
| alarm_id | machine_id | subsystem | alarm_start | alarm_end |
|---|---|---|---|---|
| 1 | 1 | hydraulics | 2025-06-02 08:00:00 | 2025-06-02 08:30:00 |
| 2 | 1 | coolant | 2025-06-02 08:20:00 | 2025-06-02 08:50:00 |
| 3 | 1 | safety_door | 2025-06-02 08:25:00 | 2025-06-02 08:35:00 |
| 4 | 1 | coolant | 2025-06-02 08:50:00 | 2025-06-02 09:00:00 |
| 5 | 1 | hydraulics | 2025-06-02 10:00:00 | 2025-06-02 10:15:00 |
| 6 | 2 | spindle | 2025-06-02 09:00:00 | 2025-06-02 09:40:00 |
| 7 | 2 | coolant | 2025-06-02 09:05:00 | 2025-06-02 09:10:00 |
Output
| machine_id | outages | down_minutes |
|---|---|---|
| 1 | 2 | 75 |
| 2 | 1 | 40 |
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_idAnother 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 →