Machines With Repeating Fault Codes — SQL Strings Problem
The PLC on every machine raises fault codes such as E-104 (spindle overload) into a fault log.
- Difficulty: Medium
- Topics: Strings, Joins, Aggregation
- Dialect: MySQL
- Problem: #265
Problem statement
The PLC on every machine raises fault codes such as E-104 (spindle overload) into a fault log. A reliability engineer wants the repeat offenders: a fault code is repeating on a machine when it was logged on that machine at least twice.
For every machine with at least one repeating code, return machine_name and repeating_codes — its repeating codes in alphabetical order, joined into one string with ', ' (comma and space) between them. Order the rows by machine_name.
Tables
Table: FloorMachine
| Column | Type |
|---|---|
| machine_id | int |
| machine_name | varchar |
Primary key: machine_id.
Machine names are unique.
Table: FaultLog
| Column | Type |
|---|---|
| fault_id | int |
| machine_id | int |
| fault_code | varchar |
| logged_at | datetime |
Primary key: fault_id.
Fault codes are E- followed by three digits.
Examples
Example 1
FloorMachine
| machine_id | machine_name |
|---|---|
| 1 | CNC-07 |
| 2 | PRESS-12 |
| 3 | WELD-21 |
FaultLog
| fault_id | machine_id | fault_code | logged_at |
|---|---|---|---|
| 1 | 1 | E-221 | 2025-01-04 10:00:00 |
| 2 | 1 | E-104 | 2025-01-05 11:30:00 |
| 3 | 1 | E-221 | 2025-01-09 09:15:00 |
| 4 | 1 | E-104 | 2025-01-12 16:40:00 |
| 5 | 1 | E-310 | 2025-01-13 08:05:00 |
| 6 | 2 | E-104 | 2025-01-06 07:20:00 |
| 7 | 3 | E-512 | 2025-01-07 22:10:00 |
| 8 | 3 | E-512 | 2025-01-08 02:45:00 |
Output
| machine_name | repeating_codes |
|---|---|
| CNC-07 | E-104, E-221 |
| WELD-21 | E-512 |
How to solve Machines With Repeating Fault Codes
Two levels of grouping. The inner query groups the fault log by machine and code and keeps the pairs with HAVING COUNT(*) >= 2 — each surviving row is one repeating code on one machine, listed once however many times it fired. A machine whose codes all fired once has no surviving row, so it is not in the answer.
The outer query joins the machine names and groups again by machine, folding the codes into a single string with GROUP_CONCAT(fault_code ORDER BY fault_code SEPARATOR ', '). The ORDER BY inside GROUP_CONCAT is what fixes the order of the codes in the string; without it the order would follow whatever order the engine read the rows in, and the comma-space separator must be given explicitly because the default is a bare comma.
The alternatives keep each log row that has a twin (same machine, same code, another id) via EXISTS, or count each pair with a window COUNT(*) OVER (PARTITION BY machine_id, fault_code), and reduce to distinct pairs before concatenating. The EXISTS form reads the log more times; the double GROUP BY is one pass plus a small second aggregation.
Reference solution (MySQL)
SELECT m.machine_name,
GROUP_CONCAT(r.fault_code ORDER BY r.fault_code SEPARATOR ', ') AS repeating_codes
FROM (
SELECT machine_id, fault_code
FROM FaultLog
GROUP BY machine_id, fault_code
HAVING COUNT(*) >= 2
) r
JOIN FloorMachine m ON m.machine_id = r.machine_id
GROUP BY m.machine_id, m.machine_name
ORDER BY m.machine_nameAnother way
SELECT m.machine_name,
GROUP_CONCAT(f.fault_code ORDER BY f.fault_code SEPARATOR ', ') AS repeating_codes
FROM FloorMachine m
JOIN (SELECT DISTINCT machine_id, fault_code FROM FaultLog f
WHERE EXISTS (SELECT 1 FROM FaultLog g WHERE g.machine_id = f.machine_id AND g.fault_code = f.fault_code AND g.fault_id <> f.fault_id)) f
ON f.machine_id = m.machine_id
GROUP BY m.machine_id, m.machine_name
ORDER BY m.machine_nameAnother way
SELECT machine_name, GROUP_CONCAT(fault_code ORDER BY fault_code SEPARATOR ', ') AS repeating_codes FROM (
SELECT DISTINCT m.machine_id, m.machine_name, f.fault_code
FROM (SELECT machine_id, fault_code, COUNT(*) OVER (PARTITION BY machine_id, fault_code) AS times FROM FaultLog) f
JOIN FloorMachine m ON m.machine_id = f.machine_id
WHERE f.times >= 2
) t GROUP BY machine_id, machine_name ORDER BY machine_name← EV Charging Peak and Off-Peak Revenue · Machine OEE per Planned Shift →