Machines With Over Two Hours of Unplanned Downtime

Medium
256.

Machines With Over Two Hours of Unplanned Downtime

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

ColumnType
machine_idint
machine_namevarchar
line_codevarchar

Primary key: machine_id.

Machine names are unique.

Table: DowntimeEvent

ColumnTypeValues
event_idint
machine_idint
started_atdatetime
ended_atdatetime
categoryenumplanned, unplanned

Primary key: event_id.

Times are whole minutes. ended_at is NULL while the machine is still down.

Example

The rows must come back in the order shown.

Machine

machine_idmachine_nameline_code
1CNC-07L1-ASSY
2PRESS-12L1-ASSY
3WELD-21L4-WELD
4MOULD-02L2-ASSY

DowntimeEvent

event_idmachine_idstarted_atended_atcategory
112024-12-02 09:00:002024-12-02 10:30:00unplanned
212024-12-14 14:10:002024-12-14 14:45:00unplanned
322024-12-05 06:00:002024-12-05 10:00:00planned
422024-12-09 11:00:002024-12-09 13:00:00unplanned
532024-11-30 22:00:002024-12-01 03:00:00unplanned
632024-12-31 23:00:002025-01-01 02:15:00unplanned
742024-12-20 08:00:00NULLunplanned

Output

machine_namestoppagesdowntime_minutes
WELD-211195
CNC-072125
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.

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

ColumnType
machine_idint
machine_namevarchar
line_codevarchar

Primary key: machine_id.

Machine names are unique.

Table: DowntimeEvent

ColumnType
event_idint
machine_idint
started_atdatetime
ended_atdatetime
categoryenum(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_idmachine_nameline_code
1CNC-07L1-ASSY
2PRESS-12L1-ASSY
3WELD-21L4-WELD
4MOULD-02L2-ASSY

DowntimeEvent

event_idmachine_idstarted_atended_atcategory
112024-12-02 09:00:002024-12-02 10:30:00unplanned
212024-12-14 14:10:002024-12-14 14:45:00unplanned
322024-12-05 06:00:002024-12-05 10:00:00planned
422024-12-09 11:00:002024-12-09 13:00:00unplanned
532024-11-30 22:00:002024-12-01 03:00:00unplanned
632024-12-31 23:00:002025-01-01 02:15:00unplanned
742024-12-20 08:00:00NULLunplanned

Output

machine_namestoppagesdowntime_minutes
WELD-211195
CNC-072125

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_name

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