Service-Due Day in Each Maintenance Cycle

Hard
272.

Service-Due Day in Each Maintenance Cycle

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

ColumnType
machine_idint
modelvarchar
service_interval_hoursint

Primary key: machine_id.

Table: RunLog

ColumnType
machine_idint
log_datedate
run_hoursint

Primary key (together): machine_id, log_date.

Hours the machine ran on that day; days it did not run may have 0 or no row.

Table: ServiceVisit

ColumnType
machine_idint
service_datedate

Primary key (together): machine_id, service_date.

Example

The rows must come back in the order shown.

Compressor

machine_idmodelservice_interval_hours
1Atlas GA-3040
2Elgi EG-2230

RunLog

machine_idlog_daterun_hours
12025-08-0116
12025-08-0218
12025-08-0310
12025-08-0420
12025-08-0522
12025-08-065
22025-08-0112
22025-08-0218
22025-08-039

ServiceVisit

machine_idservice_date
12025-08-04
22025-08-03

Output

machine_iddue_onhours_at_due
12025-08-0344
12025-08-0542
22025-08-0230
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.

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

ColumnType
machine_idint
modelvarchar
service_interval_hoursint

Primary key: machine_id.

Table: RunLog

ColumnType
machine_idint
log_datedate
run_hoursint

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

ColumnType
machine_idint
service_datedate

Primary key: machine_id, service_date.

Examples

Example 1

Compressor

machine_idmodelservice_interval_hours
1Atlas GA-3040
2Elgi EG-2230

RunLog

machine_idlog_daterun_hours
12025-08-0116
12025-08-0218
12025-08-0310
12025-08-0420
12025-08-0522
12025-08-065
22025-08-0112
22025-08-0218
22025-08-039

ServiceVisit

machine_idservice_date
12025-08-04
22025-08-03

Output

machine_iddue_onhours_at_due
12025-08-0344
12025-08-0542
22025-08-0230

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_on

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