Machines With Over Two Hours of Unplanned Downtime
The maintenance team logs every stoppage of a machine with its start and end time.
- Difficulty: Medium
- Topics: Dates, Aggregation, Joins
- Dialect: MySQL
- Problem: #256
Problem statement
The maintenance team logs every stoppage of a machine with its start and end time. Planned stoppages (changeovers, scheduled maintenance) are excluded from reliability reviews; an event still open has ended_at NULL and is ignored too.
For December 2024 (events that started from 2024-12-01 00:00:00 up to, but not including, 2025-01-01 00:00:00), return every machine whose closed unplanned stoppages add up to more than 120 minutes, with machine_name, stoppages (how many such events) and downtime_minutes (their total length in whole minutes, end minus start). Order by downtime_minutes from highest to lowest, then by machine_name.
Tables
Table: Machine
| Column | Type |
|---|---|
| machine_id | int |
| machine_name | varchar |
| line_code | varchar |
Primary key: machine_id.
Machine names are unique.
Table: DowntimeEvent
| Column | Type |
|---|---|
| event_id | int |
| machine_id | int |
| started_at | datetime |
| ended_at | datetime |
| category | enum(planned, unplanned) |
Primary key: event_id.
Times are whole minutes. ended_at is NULL while the machine is still down.
Examples
Example 1
Machine
| machine_id | machine_name | line_code |
|---|---|---|
| 1 | CNC-07 | L1-ASSY |
| 2 | PRESS-12 | L1-ASSY |
| 3 | WELD-21 | L4-WELD |
| 4 | MOULD-02 | L2-ASSY |
DowntimeEvent
| event_id | machine_id | started_at | ended_at | category |
|---|---|---|---|---|
| 1 | 1 | 2024-12-02 09:00:00 | 2024-12-02 10:30:00 | unplanned |
| 2 | 1 | 2024-12-14 14:10:00 | 2024-12-14 14:45:00 | unplanned |
| 3 | 2 | 2024-12-05 06:00:00 | 2024-12-05 10:00:00 | planned |
| 4 | 2 | 2024-12-09 11:00:00 | 2024-12-09 13:00:00 | unplanned |
| 5 | 3 | 2024-11-30 22:00:00 | 2024-12-01 03:00:00 | unplanned |
| 6 | 3 | 2024-12-31 23:00:00 | 2025-01-01 02:15:00 | unplanned |
| 7 | 4 | 2024-12-20 08:00:00 | NULL | unplanned |
Output
| machine_name | stoppages | downtime_minutes |
|---|---|---|
| WELD-21 | 1 | 195 |
| CNC-07 | 2 | 125 |
How to solve Machines With Over Two Hours of Unplanned Downtime
The question has two layers: which events count, and which machines' totals pass. The first layer is row filtering — unplanned, closed (ended_at IS NOT NULL) and started inside December by a half-open range — so it goes in WHERE. An event that started on 30 November and ran into December belongs to November by the statement's rule, and one that starts on New Year's Eve counts in full even though it ends in January.
Each remaining event's length is TIMESTAMPDIFF(MINUTE, started_at, ended_at). Grouping by the machine gives COUNT(*) stoppages and the summed minutes; the 120-minute threshold is a condition on that sum, so it lives in HAVING, and > 120 leaves a machine at exactly two hours out. An open event would make its length NULL, which SUM would skip — filtering it explicitly says what is meant.
The alternative aggregates in a derived table first and joins the names afterwards, which touches Machine only for the machines that passed. Either is one scan of the December events and a sort of the few survivors.
Reference solution (MySQL)
SELECT m.machine_name,
COUNT(*) AS stoppages,
SUM(TIMESTAMPDIFF(MINUTE, d.started_at, d.ended_at)) AS downtime_minutes
FROM DowntimeEvent d
JOIN Machine m ON m.machine_id = d.machine_id
WHERE d.category = 'unplanned'
AND d.ended_at IS NOT NULL
AND d.started_at >= '2024-12-01 00:00:00'
AND d.started_at < '2025-01-01 00:00:00'
GROUP BY m.machine_id, m.machine_name
HAVING SUM(TIMESTAMPDIFF(MINUTE, d.started_at, d.ended_at)) > 120
ORDER BY downtime_minutes DESC, m.machine_nameAnother way
SELECT machine_name, stoppages, downtime_minutes FROM (
SELECT machine_id, COUNT(*) AS stoppages, SUM(TIMESTAMPDIFF(SECOND, started_at, ended_at)) DIV 60 AS downtime_minutes
FROM DowntimeEvent
WHERE category = 'unplanned' AND ended_at IS NOT NULL AND YEAR(started_at) = 2024 AND MONTH(started_at) = 12
GROUP BY machine_id
) t JOIN Machine USING (machine_id)
WHERE downtime_minutes > 120
ORDER BY downtime_minutes DESC, machine_name← Completed Work Orders and Good Units per Line · Sensor Threshold Breach Rate →