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
| Column | Type |
|---|---|
| machine_id | int |
| machine_name | varchar |
| ideal_cycle_secs | int |
Primary key: machine_id.
ideal_cycle_secs is the design time to make one part. Machine names are unique.
Table: ShiftPlan
| Column | Type |
|---|---|
| machine_id | int |
| shift_date | date |
| planned_minutes | int |
Primary key: machine_id, shift_date.
One row per machine per shift it was scheduled to run (breaks already excluded).
Table: Stoppage
| Column | Type |
|---|---|
| stoppage_id | int |
| machine_id | int |
| shift_date | date |
| minutes | int |
Primary key: stoppage_id.
Every stoppage belongs to a planned shift; a shift can have several.
Table: PartCount
| Column | Type |
|---|---|
| count_id | int |
| machine_id | int |
| shift_date | date |
| total_units | int |
| good_units | int |
Primary key: count_id.
Counter readings for an hour bucket of a planned shift; good_units ≤ total_units.
Examples
Example 1
OeeMachine
| machine_id | machine_name | ideal_cycle_secs |
|---|---|---|
| 1 | CNC-07 | 60 |
| 2 | PRESS-12 | 12 |
ShiftPlan
| machine_id | shift_date | planned_minutes |
|---|---|---|
| 1 | 2025-04-07 | 480 |
| 1 | 2025-04-08 | 480 |
| 2 | 2025-04-07 | 450 |
| 2 | 2025-04-08 | 450 |
Stoppage
| stoppage_id | machine_id | shift_date | minutes |
|---|---|---|---|
| 1 | 1 | 2025-04-07 | 30 |
| 2 | 1 | 2025-04-07 | 18 |
| 3 | 2 | 2025-04-07 | 45 |
| 4 | 1 | 2025-04-08 | 120 |
PartCount
| count_id | machine_id | shift_date | total_units | good_units |
|---|---|---|---|---|
| 1 | 1 | 2025-04-07 | 200 | 196 |
| 2 | 1 | 2025-04-07 | 180 | 170 |
| 3 | 2 | 2025-04-07 | 1800 | 1750 |
| 4 | 1 | 2025-04-08 | 240 | 240 |
| 5 | 1 | 2025-04-07 | 0 | 0 |
Output
| machine_name | shift_date | availability_pct | quality_pct | oee_pct |
|---|---|---|---|---|
| CNC-07 | 2025-04-07 | 90 | 96.3 | 76.3 |
| CNC-07 | 2025-04-08 | 75 | 100 | 50 |
| PRESS-12 | 2025-04-07 | 90 | 97.2 | 77.8 |
| PRESS-12 | 2025-04-08 | 100 | NULL | 0 |
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_dateAnother 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 →