Feeder Downtime After Merging Overlapping Outages

Hard
517.

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. 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

ColumnType
report_idint
feeder_codevarchar
started_atdatetime
restored_atdatetime

Primary key: report_id.

One reported outage; times are whole minutes and restored_at is always after started_at.

Example

The rows must come back in the order shown.

OutageReport

report_idfeeder_codestarted_atrestored_at
1BLR-F112024-07-02 08:00:002024-07-02 10:00:00
2BLR-F112024-07-02 09:30:002024-07-02 11:15:00
3BLR-F112024-07-02 11:15:002024-07-02 12:00:00
4BLR-F112024-07-02 14:00:002024-07-02 14:45:00
5BLR-F122024-07-02 10:00:002024-07-02 13:00:00
6BLR-F122024-07-02 10:30:002024-07-02 11:00:00
7BLR-F122024-07-02 23:30:002024-07-03 01:10:00
8BLR-F272024-07-03 09:00:002024-07-03 09:20:00

Output

feeder_codeincidentsdowntime_minutes
BLR-F112285
BLR-F122280
BLR-F27120
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.

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

ColumnType
report_idint
feeder_codevarchar
started_atdatetime
restored_atdatetime

Primary key: report_id.

One reported outage; times are whole minutes and restored_at is always after started_at.

Examples

Example 1

OutageReport

report_idfeeder_codestarted_atrestored_at
1BLR-F112024-07-02 08:00:002024-07-02 10:00:00
2BLR-F112024-07-02 09:30:002024-07-02 11:15:00
3BLR-F112024-07-02 11:15:002024-07-02 12:00:00
4BLR-F112024-07-02 14:00:002024-07-02 14:45:00
5BLR-F122024-07-02 10:00:002024-07-02 13:00:00
6BLR-F122024-07-02 10:30:002024-07-02 11:00:00
7BLR-F122024-07-02 23:30:002024-07-03 01:10:00
8BLR-F272024-07-03 09:00:002024-07-03 09:20:00

Output

feeder_codeincidentsdowntime_minutes
BLR-F112285
BLR-F122280
BLR-F27120

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_code

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