Machine OEE per Planned Shift

Hard
266.

Machine OEE per Planned Shift

Overall Equipment Effectiveness (OEE) is the plant's headline metric. For one machine on one planned shift, using the shift's planned minutes, the stoppage minutes logged against that shift, and the part counter's readings (a shift can have several counter rows, one per hour bucket):

  • availability_pct = 100 × (planned minutes − stoppage minutes) ÷ planned minutes;
  • quality_pct = 100 × good units ÷ total units, or NULL when the shift counted no units at all;
  • oee_pct = 100 × good units × ideal_cycle_secs ÷ (planned minutes × 60) — 0 when no good units were counted.

Return one row for every planned shift with machine_name, shift_date, availability_pct, quality_pct and oee_pct, each percentage rounded to one decimal. A shift with no stoppage has 100% availability. Order by machine_name, then shift_date.

Tables

Table: OeeMachine

ColumnType
machine_idint
machine_namevarchar
ideal_cycle_secsint

Primary key: machine_id.

ideal_cycle_secs is the design time to make one part. Machine names are unique.

Table: ShiftPlan

ColumnType
machine_idint
shift_datedate
planned_minutesint

Primary key (together): machine_id, shift_date.

One row per machine per shift it was scheduled to run (breaks already excluded).

Table: Stoppage

ColumnType
stoppage_idint
machine_idint
shift_datedate
minutesint

Primary key: stoppage_id.

Every stoppage belongs to a planned shift; a shift can have several.

Table: PartCount

ColumnType
count_idint
machine_idint
shift_datedate
total_unitsint
good_unitsint

Primary key: count_id.

Counter readings for an hour bucket of a planned shift; good_units ≤ total_units.

Example

The rows must come back in the order shown.

OeeMachine

machine_idmachine_nameideal_cycle_secs
1CNC-0760
2PRESS-1212

ShiftPlan

machine_idshift_dateplanned_minutes
12025-04-07480
12025-04-08480
22025-04-07450
22025-04-08450

Stoppage

stoppage_idmachine_idshift_dateminutes
112025-04-0730
212025-04-0718
322025-04-0745
412025-04-08120

PartCount

count_idmachine_idshift_datetotal_unitsgood_units
112025-04-07200196
212025-04-07180170
322025-04-0718001750
412025-04-08240240
512025-04-0700

Output

machine_nameshift_dateavailability_pctquality_pctoee_pct
CNC-072025-04-079096.376.3
CNC-072025-04-087510050
PRESS-122025-04-079097.277.8
PRESS-122025-04-08100NULL0
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.

Machine OEE per Planned Shift — SQL Joins Problem

Overall Equipment Effectiveness (OEE) is the plant's headline metric.

  • Difficulty: Hard
  • Topics: Joins, Aggregation
  • Dialect: MySQL
  • Problem: #266

Problem statement

Overall Equipment Effectiveness (OEE) is the plant's headline metric. For one machine on one planned shift, using the shift's planned minutes, the stoppage minutes logged against that shift, and the part counter's readings (a shift can have several counter rows, one per hour bucket):

  • availability_pct = 100 × (planned minutes − stoppage minutes) ÷ planned minutes;
  • quality_pct = 100 × good units ÷ total units, or NULL when the shift counted no units at all;
  • oee_pct = 100 × good units × ideal_cycle_secs ÷ (planned minutes × 60) — 0 when no good units were counted.

Return one row for every planned shift with machine_name, shift_date, availability_pct, quality_pct and oee_pct, each percentage rounded to one decimal. A shift with no stoppage has 100% availability. Order by machine_name, then shift_date.

Tables

Table: OeeMachine

ColumnType
machine_idint
machine_namevarchar
ideal_cycle_secsint

Primary key: machine_id.

ideal_cycle_secs is the design time to make one part. Machine names are unique.

Table: ShiftPlan

ColumnType
machine_idint
shift_datedate
planned_minutesint

Primary key: machine_id, shift_date.

One row per machine per shift it was scheduled to run (breaks already excluded).

Table: Stoppage

ColumnType
stoppage_idint
machine_idint
shift_datedate
minutesint

Primary key: stoppage_id.

Every stoppage belongs to a planned shift; a shift can have several.

Table: PartCount

ColumnType
count_idint
machine_idint
shift_datedate
total_unitsint
good_unitsint

Primary key: count_id.

Counter readings for an hour bucket of a planned shift; good_units ≤ total_units.

Examples

Example 1

OeeMachine

machine_idmachine_nameideal_cycle_secs
1CNC-0760
2PRESS-1212

ShiftPlan

machine_idshift_dateplanned_minutes
12025-04-07480
12025-04-08480
22025-04-07450
22025-04-08450

Stoppage

stoppage_idmachine_idshift_dateminutes
112025-04-0730
212025-04-0718
322025-04-0745
412025-04-08120

PartCount

count_idmachine_idshift_datetotal_unitsgood_units
112025-04-07200196
212025-04-07180170
322025-04-0718001750
412025-04-08240240
512025-04-0700

Output

machine_nameshift_dateavailability_pctquality_pctoee_pct
CNC-072025-04-079096.376.3
CNC-072025-04-087510050
PRESS-122025-04-079097.277.8
PRESS-122025-04-08100NULL0

How to solve Machine OEE per Planned Shift

The heart of the problem is the fan-out trap. A shift with two stoppages and three counter rows joined to both tables at once becomes 2 × 3 = 6 rows, so the stoppage minutes would be counted three times and the units twice. The fix is to aggregate each many-side table to the grain of the answer — one row per (machine_id, shift_date) — in its own CTE, and only then join them to ShiftPlan.

Every planned shift must appear, so the plan is the driving table and both aggregates are LEFT JOINed. The missing cases then follow the statement: no stoppage means COALESCE(stop_minutes, 0) and 100% availability; no counts means the quality ratio has no denominator — NULLIF(total_units, 0) makes it NULL, which also covers counter rows that all read zero; and OEE uses COALESCE(good_units, 0), giving 0.

OEE is the product of availability, performance and quality, but the three ratios cancel to good units × ideal cycle time ÷ planned time. Computing it as that single division avoids multiplying three already-rounded percentages. The correlated-subquery alternative computes each sum per shift instead of per join; it is correct for the same reason — each subquery sums one table alone — but scans the many-side tables once per shift.

Reference solution (MySQL)

WITH down AS (
  SELECT machine_id, shift_date, SUM(minutes) AS stop_minutes
  FROM Stoppage GROUP BY machine_id, shift_date
), made AS (
  SELECT machine_id, shift_date, SUM(total_units) AS total_units, SUM(good_units) AS good_units
  FROM PartCount GROUP BY machine_id, shift_date
)
SELECT m.machine_name, p.shift_date,
       ROUND(100 * (p.planned_minutes - COALESCE(d.stop_minutes, 0)) / p.planned_minutes, 1) AS availability_pct,
       ROUND(100 * mk.good_units / NULLIF(mk.total_units, 0), 1) AS quality_pct,
       ROUND(100 * COALESCE(mk.good_units, 0) * m.ideal_cycle_secs / (p.planned_minutes * 60), 1) AS oee_pct
FROM ShiftPlan p
JOIN OeeMachine m ON m.machine_id = p.machine_id
LEFT JOIN down d ON d.machine_id = p.machine_id AND d.shift_date = p.shift_date
LEFT JOIN made mk ON mk.machine_id = p.machine_id AND mk.shift_date = p.shift_date
ORDER BY m.machine_name, p.shift_date

Another way

SELECT m.machine_name, p.shift_date,
       ROUND(100 * (p.planned_minutes - (SELECT COALESCE(SUM(s.minutes), 0) FROM Stoppage s
                                          WHERE s.machine_id = p.machine_id AND s.shift_date = p.shift_date)) / p.planned_minutes, 1) AS availability_pct,
       ROUND(100 * (SELECT SUM(c.good_units) FROM PartCount c WHERE c.machine_id = p.machine_id AND c.shift_date = p.shift_date)
             / NULLIF((SELECT SUM(c.total_units) FROM PartCount c WHERE c.machine_id = p.machine_id AND c.shift_date = p.shift_date), 0), 1) AS quality_pct,
       ROUND(100 * (SELECT COALESCE(SUM(c.good_units), 0) FROM PartCount c WHERE c.machine_id = p.machine_id AND c.shift_date = p.shift_date)
             * m.ideal_cycle_secs / (p.planned_minutes * 60), 1) AS oee_pct
FROM ShiftPlan p JOIN OeeMachine m ON m.machine_id = p.machine_id
ORDER BY m.machine_name, p.shift_date

← Machines With Repeating Fault Codes · Longest Defect-Free Batch Streak per Line →