Service-Due Day in Each Maintenance Cycle — SQL Problem
Each compressor must be serviced after every service_interval_hours hours of running.
- Difficulty: Hard
- Topics: Window Functions, Subqueries, Conditional Logic
- Dialect: MySQL
- Problem: #272
Problem statement
Each compressor must be serviced after every service_interval_hours hours of running. Daily run hours are logged; a service done on a date resets the counter, and it happens before that day's run, so that day's hours already count toward the new cycle. The hours logged before a machine's first service form its first cycle.
For every cycle in which a machine's running hours reached or passed its interval, return the first day it did so: machine_id, due_on (that log date) and hours_at_due (the cycle's cumulative run hours at the end of that day). A cycle that never reaches the interval is not listed. Order by machine_id, then due_on.
Tables
Table: Compressor
| Column | Type |
|---|---|
| machine_id | int |
| model | varchar |
| service_interval_hours | int |
Primary key: machine_id.
Table: RunLog
| Column | Type |
|---|---|
| machine_id | int |
| log_date | date |
| run_hours | int |
Primary key: machine_id, log_date.
Hours the machine ran on that day; days it did not run may have 0 or no row.
Table: ServiceVisit
| Column | Type |
|---|---|
| machine_id | int |
| service_date | date |
Primary key: machine_id, service_date.
Examples
Example 1
Compressor
| machine_id | model | service_interval_hours |
|---|---|---|
| 1 | Atlas GA-30 | 40 |
| 2 | Elgi EG-22 | 30 |
RunLog
| machine_id | log_date | run_hours |
|---|---|---|
| 1 | 2025-08-01 | 16 |
| 1 | 2025-08-02 | 18 |
| 1 | 2025-08-03 | 10 |
| 1 | 2025-08-04 | 20 |
| 1 | 2025-08-05 | 22 |
| 1 | 2025-08-06 | 5 |
| 2 | 2025-08-01 | 12 |
| 2 | 2025-08-02 | 18 |
| 2 | 2025-08-03 | 9 |
ServiceVisit
| machine_id | service_date |
|---|---|
| 1 | 2025-08-04 |
| 2 | 2025-08-03 |
Output
| machine_id | due_on | hours_at_due |
|---|---|---|
| 1 | 2025-08-03 | 44 |
| 1 | 2025-08-05 | 42 |
| 2 | 2025-08-02 | 30 |
How to solve Service-Due Day in Each Maintenance Cycle
This is a running total with resets. A window SUM cannot reset by itself, so the trick is to give it a partition that changes at each reset. A row's cycle number is the count of that machine's services dated on or before its log date — a correlated COUNT, or a LEFT JOIN plus GROUP BY. Because a service on day d counts for day d (<=), that day's hours fall into the new cycle, as the statement says; logs before any service get cycle 0.
SUM(run_hours) OVER (PARTITION BY machine_id, cycle_no ORDER BY log_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) is then the hours since the last service at the end of each day. The first day of a cycle to reach the limit is the one where the total is >= lim while the total before that day's hours, cum - run_hours, was still below it. Since hours are never negative the running total only grows, so this happens at most once per cycle.
The alternative keeps every row at or past the limit and takes the earliest per cycle with ROW_NUMBER. Both sort each machine's logs once; the cycle numbering is the only quadratic part, and services are few.
Reference solution (MySQL)
WITH cycled AS (
SELECT r.machine_id, r.log_date, r.run_hours,
(SELECT COUNT(*) FROM ServiceVisit v WHERE v.machine_id = r.machine_id AND v.service_date <= r.log_date) AS cycle_no
FROM RunLog r
), running AS (
SELECT c.machine_id, c.log_date, c.run_hours, k.service_interval_hours AS lim,
SUM(c.run_hours) OVER (PARTITION BY c.machine_id, c.cycle_no ORDER BY c.log_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum
FROM cycled c
JOIN Compressor k ON k.machine_id = c.machine_id
)
SELECT machine_id, log_date AS due_on, cum AS hours_at_due
FROM running
WHERE cum >= lim AND cum - run_hours < lim
ORDER BY machine_id, due_onAnother way
WITH cycled AS (
SELECT r.machine_id, r.log_date, r.run_hours, COUNT(v.service_date) AS cycle_no
FROM RunLog r LEFT JOIN ServiceVisit v ON v.machine_id = r.machine_id AND v.service_date <= r.log_date
GROUP BY r.machine_id, r.log_date, r.run_hours
), running AS (
SELECT c.*, k.service_interval_hours AS lim,
SUM(run_hours) OVER (PARTITION BY c.machine_id, cycle_no ORDER BY log_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum
FROM cycled c JOIN Compressor k ON k.machine_id = c.machine_id
), hit AS (
SELECT machine_id, log_date, cum, ROW_NUMBER() OVER (PARTITION BY machine_id, cycle_no ORDER BY log_date) AS k
FROM running WHERE cum >= lim
)
SELECT machine_id, log_date AS due_on, cum AS hours_at_due FROM hit WHERE k = 1 ORDER BY machine_id, due_on← Solar Daily Output With Missing Days Filled · Ad Campaigns Still Live at the Quarter Close →