Feeder Downtime After Merging Overlapping Outages
Outages on a feeder are logged from several sources — the SCADA system, the call centre, the line crew — so the reports overlap.
- Difficulty: Hard
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #517
Problem statement
Outages on a feeder are logged from several sources — the SCADA system, the call centre, the line crew — so the reports overlap. For the reliability index, the overlapping reports of a feeder must be merged: two reports belong to one incident when their time ranges overlap or touch (one is restored exactly when the next starts), directly or through a chain of other reports.
Return one row per feeder with the columns feeder_code, incidents (the number of merged incidents) and downtime_minutes (the total minutes the feeder was down, each minute counted once). Order by feeder_code.
Tables
Table: OutageReport
| Column | Type |
|---|---|
| report_id | int |
| feeder_code | varchar |
| started_at | datetime |
| restored_at | datetime |
Primary key: report_id.
One reported outage; times are whole minutes and restored_at is always after started_at.
Examples
Example 1
OutageReport
| report_id | feeder_code | started_at | restored_at |
|---|---|---|---|
| 1 | BLR-F11 | 2024-07-02 08:00:00 | 2024-07-02 10:00:00 |
| 2 | BLR-F11 | 2024-07-02 09:30:00 | 2024-07-02 11:15:00 |
| 3 | BLR-F11 | 2024-07-02 11:15:00 | 2024-07-02 12:00:00 |
| 4 | BLR-F11 | 2024-07-02 14:00:00 | 2024-07-02 14:45:00 |
| 5 | BLR-F12 | 2024-07-02 10:00:00 | 2024-07-02 13:00:00 |
| 6 | BLR-F12 | 2024-07-02 10:30:00 | 2024-07-02 11:00:00 |
| 7 | BLR-F12 | 2024-07-02 23:30:00 | 2024-07-03 01:10:00 |
| 8 | BLR-F27 | 2024-07-03 09:00:00 | 2024-07-03 09:20:00 |
Output
| feeder_code | incidents | downtime_minutes |
|---|---|---|
| BLR-F11 | 2 | 285 |
| BLR-F12 | 2 | 280 |
| BLR-F27 | 1 | 20 |
How to solve Feeder Downtime After Merging Overlapping Outages
Merging overlapping intervals is a gaps-and-islands problem on ranges. Order each feeder's reports by started_at (with report_id making the order total). A report belongs to the incident in progress when it starts no later than the latest restoration among all earlier reports — not just the previous one, because a long report can cover several short ones after it. MAX(restored_at) OVER (… ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) computes that latest end; it is NULL for the first report.
A report opens a new incident exactly when prev_end is NULL or started_at > prev_end. The comparison is strict, so a report starting at the very minute another is restored joins it — the touching rule. A running SUM of those 0/1 flags numbers the incidents, and grouping by it gives each incident's first start and last restoration. Its length is TIMESTAMPDIFF(MINUTE, s, e); summing those per feeder counts every minute once, whereas summing the raw reports would double-count overlaps.
The alternative finds the start times that no earlier report covers (NOT EXISTS), assigns each report to the latest such start at or before it, and measures from there. It is quadratic per feeder; the window version is one sort.
Reference solution (MySQL)
WITH ordered AS (
SELECT report_id, feeder_code, started_at, restored_at,
MAX(restored_at) OVER (PARTITION BY feeder_code ORDER BY started_at, report_id
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_end
FROM OutageReport
), grouped AS (
SELECT feeder_code, started_at, restored_at,
SUM(CASE WHEN prev_end IS NULL OR started_at > prev_end THEN 1 ELSE 0 END)
OVER (PARTITION BY feeder_code ORDER BY started_at, report_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS incident
FROM ordered
), merged AS (
SELECT feeder_code, incident, MIN(started_at) AS s, MAX(restored_at) AS e
FROM grouped
GROUP BY feeder_code, incident
)
SELECT feeder_code, COUNT(*) AS incidents, SUM(TIMESTAMPDIFF(MINUTE, s, e)) AS downtime_minutes
FROM merged
GROUP BY feeder_code
ORDER BY feeder_codeAnother way
WITH starts AS (
SELECT DISTINCT a.feeder_code, a.started_at
FROM OutageReport a
WHERE NOT EXISTS (
SELECT 1 FROM OutageReport b
WHERE b.feeder_code = a.feeder_code AND b.started_at < a.started_at AND b.restored_at >= a.started_at
)
), tagged AS (
SELECT r.feeder_code, r.restored_at,
(SELECT MAX(s.started_at) FROM starts s
WHERE s.feeder_code = r.feeder_code AND s.started_at <= r.started_at) AS incident_start
FROM OutageReport r
), merged AS (
SELECT feeder_code, incident_start, MAX(restored_at) AS incident_end
FROM tagged GROUP BY feeder_code, incident_start
)
SELECT feeder_code, COUNT(*) AS incidents,
SUM(TIMESTAMPDIFF(MINUTE, incident_start, incident_end)) AS downtime_minutes
FROM merged GROUP BY feeder_code ORDER BY feeder_code← Longest Streak of Heavy Data Days · Median Completed Call Duration per Tower →