Machines With Repeating Fault Codes

Medium
265.

Machines With Repeating Fault Codes

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

ColumnType
machine_idint
machine_namevarchar

Primary key: machine_id.

Machine names are unique.

Table: FaultLog

ColumnType
fault_idint
machine_idint
fault_codevarchar
logged_atdatetime

Primary key: fault_id.

Fault codes are E- followed by three digits.

Example

The rows must come back in the order shown.

FloorMachine

machine_idmachine_name
1CNC-07
2PRESS-12
3WELD-21

FaultLog

fault_idmachine_idfault_codelogged_at
11E-2212025-01-04 10:00:00
21E-1042025-01-05 11:30:00
31E-2212025-01-09 09:15:00
41E-1042025-01-12 16:40:00
51E-3102025-01-13 08:05:00
62E-1042025-01-06 07:20:00
73E-5122025-01-07 22:10:00
83E-5122025-01-08 02:45:00

Output

machine_namerepeating_codes
CNC-07E-104, E-221
WELD-21E-512
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 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

ColumnType
machine_idint
machine_namevarchar

Primary key: machine_id.

Machine names are unique.

Table: FaultLog

ColumnType
fault_idint
machine_idint
fault_codevarchar
logged_atdatetime

Primary key: fault_id.

Fault codes are E- followed by three digits.

Examples

Example 1

FloorMachine

machine_idmachine_name
1CNC-07
2PRESS-12
3WELD-21

FaultLog

fault_idmachine_idfault_codelogged_at
11E-2212025-01-04 10:00:00
21E-1042025-01-05 11:30:00
31E-2212025-01-09 09:15:00
41E-1042025-01-12 16:40:00
51E-3102025-01-13 08:05:00
62E-1042025-01-06 07:20:00
73E-5122025-01-07 22:10:00
83E-5122025-01-08 02:45:00

Output

machine_namerepeating_codes
CNC-07E-104, E-221
WELD-21E-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_name

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

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