Machines on a Repeating Maintenance Rotation
Problem
A maintenance system logs each task performed on a machine, in date order. A machine follows a 'repeating rotation' when its tasks cycle through at least three distinct task types in a fixed order that repeats for at least two full cycles (so at least six logged tasks), with no gap longer than two days between consecutive tasks. For each such machine, report the length of one cycle (number of tasks before the pattern repeats) and the total crew hours across the chained tasks. Sort by cycle length descending, then by total crew hours descending.
Tables
Example rows — the live problem includes the full dataset.
| machine_id | machine_name | plant |
|---|---|---|
| 1 | Lathe One | North |
| 2 | Press Two | North |
| 3 | Mill Three | South |
| log_id | machine_id | task | done_on | crew_hours |
|---|---|---|---|---|
| 1 | 1 | oil | 2024-05-01 | 2 |
| 2 | 1 | belt | 2024-05-02 | 3 |
| 3 | 1 | filter | 2024-05-03 | 2.5 |
Expected output
Your answer should return 2 rows with the columns machine_name, cycle_length, total_hours.
Starter code (SQL)
SELECT *
FROM machines;Solve this SQL question free
Write SQL and run it instantly in your browser — even on your phone. No signup needed to try.
Solution & explanation
Create a free account to unlock the optimal solution, a step-by-step explanation, and the hidden test cases that grade your answer.
Sign up free to unlock