To measure how long a site was down, merge overlapping incident intervals for that site and add the lengths of the merged spans. Two tickets open during the same minute can describe one minute of downtime. This calculation assumes each ticket represents a confirmed outage for its entire recorded interval.
Why does the previous ticket’s end give the wrong answer?
Suppose a Boston warehouse loses its order terminal from minute 0 to 60. A second ticket covers minutes 10 to 20, and a third covers 30 to 75. When the third ticket opens, the second has closed, but the first outage is still in progress. Comparing 30 with the immediately preceding end of 20 would split one continuous outage. Compare 30 with the furthest end reached so far, which is 60.
Run the interval merge in SQLite
The fixture uses elapsed minutes from a shared UTC origin, with inclusive starts and exclusive ends. All intervals have a positive duration and each ticket ID is unique. A separate Leeds outage overlaps part of Boston’s timeline. Each warehouse gets its own total.
WITH incidents(site,id,start_min,end_min) AS (
VALUES ('Boston',1,0,60), ('Boston',2,10,20),
('Boston',3,30,75), ('Boston',4,90,100),
('Leeds',5,20,40)
), edges AS (
SELECT *, MAX(end_min) OVER (
PARTITION BY site ORDER BY start_min,id
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
) AS prior_end FROM incidents
), labeled AS (
SELECT *, SUM(CASE WHEN prior_end IS NULL
OR start_min > prior_end THEN 1 ELSE 0 END) OVER (
PARTITION BY site ORDER BY start_min,id
ROWS UNBOUNDED PRECEDING
) AS island FROM edges
), spans AS (
SELECT site,island,MIN(start_min) AS lo,MAX(end_min) AS hi
FROM labeled GROUP BY site,island
)
SELECT site,SUM(hi-lo) AS downtime_minutes
FROM spans GROUP BY site ORDER BY site;
Reconcile the two totals
Boston has two merged spans, [0,75) and [90,100). Their lengths add to 85 minutes. Adding the four individual ticket lengths would give 125 minutes, overstating downtime by 40. Leeds contributes 20 minutes. Across the two locations that gives 105 warehouse-minutes. If the question is how long at least one location was down, Leeds falls entirely within Boston’s first outage, giving 85 elapsed minutes across the network.
The first window ends one row before the current ticket so the ticket cannot match itself. The running sum assigns a new group when a start falls beyond all preceding ends. Touching intervals share a group under this rule. Counting them as separate incidents would change the incident count while preserving the total duration.
What belongs in a daily report?
Clip each interval to the reporting window before merging. For example, an outage from 23:50 to 00:10 contributes ten minutes to each day when both timestamps use the reporting timezone. Include incidents already open at the start of the day. For open tickets, record a fixed observation cutoff and mark the duration provisional. A ticket queue that also contains slowdowns needs an explicit outage classification before this calculation can support an availability metric.
See the SQLite window frame documentation for the preceding-row boundary. For related exercises, compare grouping point events into sessions and handling midnight report boundaries.