Quality Metrics and Test Reporting Questions

Direct answer

I would not react to the slope. First I would check that the measurement itself did not change, then compare the drop with the normal day-to-day wobble of this suite, then slice the drop to see whether it is concentrated in a few tests, one component or one date. Only then do I call it real. For next time, I turn that comparison into an automatic alert built on the suite's own history.

Step 1: did what we measure change?

• Was the set of tests different (new tests added that fail more, old ones deleted, more skipped or quarantined, meaning moved out of the gate that blocks merges)? Recompute the rate on the tests that existed at the start of the window. A falling overall rate with a flat rate on that fixed set means the mix changed, not the quality.
• Did the definition, retry policy, runner image or test data change? Infrastructure changes often show up as a spread-out drop across many unrelated suites.

Step 2: is it more than ordinary variation?

Measure the normal wobble from a stable baseline: the mean and standard deviation (sd, how far daily values typically stray from the mean) of the daily pass rate. Treat the day or the run, not the individual test, as the unit, because failures are clumped: one broken dependency can fail hundreds of tests at once, so a textbook binomial error bar (the range you would expect if every test failed independently, like coin flips) would be far too narrow. Adjust for known patterns: compare weekdays with weekdays if daily builds differ at weekends, pool sparse tests over a longer window instead of judging them daily, and use rates rather than counts when suite sizes differ.

Step 3: slice it. A genuine quality loss usually concentrates: a handful of tests, one owning team, one component, or one merge date. Then reproduce a sample of the new failures on a clean environment. Real bugs reproduce; test rot and infrastructure trouble often do not.

Step 4: automate the flag. This is a control chart approach: plot the series against limits computed from its own history and alarm when it leaves them. If you adopt only one rule first, take 8 in a row below the mean, then add the 3 sd limit for crashes. Three simple rules on the daily series, each with a different trade-off:

Change-point methods, which estimate when a shift began, are rarely needed in interviews and are useful after an alarm to date the change against releases; pairing that with the rules above is enough for most teams.

Worked example (synthetic data, seed pinned; run it unchanged)

import random
from statistics import mean, stdev

rng = random.Random(7)
# Synthetic daily pass rates (percent): 28 stable baseline days, then 21 days of slow decline.
baseline = [rng.gauss(97.0, 0.6) for _ in range(28)]
recent = [rng.gauss(97.0 - 0.06 * d, 0.6) for d in range(1, 22)]
series = baseline + recent

mu, sd = mean(baseline), stdev(baseline)
lower = mu - 3 * sd
print(f"baseline mean={mu:.2f} sd={sd:.2f} lower limit={lower:.2f}")

# Rule 1: any single day below the 3-sigma lower limit
first_limit = next((i for i, x in enumerate(series) if x < lower), None)
# Rule 2: 8 consecutive days below the baseline mean
first_run = None
streak = 0
for i, x in enumerate(series):
    streak = streak + 1 if x < mu else 0
    if streak == 8:
        first_run = i
        break
# Rule 3: one-sided CUSUM (allowance k = 0.5 sd, decision threshold h = 4 sd)
k, h, s, first_cusum = 0.5 * sd, 4 * sd, 0.0, None
for i, x in enumerate(series):
    s = min(0.0, s + (x - mu) + k)
    if s < -h:
        first_cusum = i
        break

def day(i):
    return "none" if i is None else f"day {i + 1}"

print("drift starts: day 29")
print("3-sigma limit alarm:", day(first_limit))
print("8-in-a-row below mean:", day(first_run))
print("CUSUM alarm:", day(first_cusum))

baseline mean=96.98 sd=0.51 lower limit=95.46
drift starts: day 29
3-sigma limit alarm: day 44
8-in-a-row below mean: day 38
CUSUM alarm: day 38

How the CUSUM line reads: each day it adds (today's value minus the mean, plus the allowance k). Days near the mean add about zero, and the min(0.0, ...) keeps the total from drifting upward, so only shortfalls accumulate. When the running total drops below minus h, the shortfall has persisted long enough to alarm. k = 0.5 sd ignores tiny wobble; h = 4 sd is a common starting point, tuned on past data.

Days 1 to 28 are a stable baseline; from day 29 the true rate slides by 0.06 points a day. None of the rules fired during the baseline. The 8-in-a-row rule and CUSUM alarm on day 38, roughly 0.6 points below baseline, while the strict 3-sd limit needs until day 44. This is one synthetic series, so the exact days illustrate the trade-off, not a promise.

Trade-offs and pitfalls

• Tighter rules alert sooner but cry wolf more, and an alert nobody trusts is ignored. Tune on past data and review alert precision monthly (the share of alerts that turned out to be real problems; for example 3 real out of 10 alerts is 30 percent, too noisy).
• Sending the alert with the slice (which tests, which team, first bad date) makes it actionable; a bare percentage prompts a meeting.
• The method assumes the past is a fair baseline. After a deliberate change, such as a suite migration, reset the baseline.

Direct answer

Notify the team channel for failures that need a human to look but not right now, page (wake or interrupt an on-call person through a pager or phone alert) only when the failure blocks a release or signals production risk, and suppress everything that is noise or already known. I would deduplicate by failure signature (a fingerprint of the error and location) plus branch, so one root cause produces one alert, not one per test. Every number below is a starting value to tune against a few weeks of your own alert history.

The three tiers

De-duplication windows

• Channel notification: one thread per signature and branch. Repeats inside a 4-hour window add a reply ("seen 6 more times") rather than a new message. Reason: a nightly job might legitimately fail again the next morning, but not within hours as a new event.
• Page: one open incident per signature until it is acknowledged and resolved. Repeats never re-page, they append to the incident.

Why 5 points: on a suite with a few hundred tests, a handful of flaky tests can move the pass rate by a point or two on an ordinary night, so a 5-point drop is comfortably outside normal wobble. If your own 14-day history shows wider swings, widen the margin. Tune it until at most a few channel alerts a week are false alarms.

Escalation and back-off for repeated alerts

(On-call is the person or rotation responsible for responding out of hours; primary is the first person paged, secondary is the backup. Acknowledgement means that person confirms in the paging tool that they have seen the alert and are working on it.)

1. Page the primary on-call. No acknowledgement after 15 minutes: page the secondary.
2. Still no acknowledgement after 30 minutes: notify the engineering manager.
3. For a channel alert that is still open: reminder after 4 hours, then 24 hours, then move it into a daily digest. Back-off stops a stale alert from becoming wallpaper.

Flaky tests (pointer only)

A flaky test is one that passes and fails on the same code. Detecting and repairing flaky tests is a separate discipline. Here the rules only consume its output: a test with a quarantine flag is suppressed from paging and channel alerts but still recorded, and a quarantined test that fails while running alone still shows on the dashboard. Rerun-on-failure is a suppression aid, not a fix.

Worked example

The main-branch suite runs every hour, plus the longer nightly suite. At 02:10 the main run fails: 30 tests, 3 signatures. After auto-rerun 26 still fail and all 26 share signature A. The next hourly run fails identically at 03:10 (second consecutive run), so the channel gets one message: "Signature A, 26 tests, 2 runs, owner: payments". The smoke test after the 09:00 deploy passes, so nobody is paged. Had that smoke test failed twice, payments on-call would be paged once, with the same signature attached.

Trade-offs and pitfalls

• Lower thresholds catch bugs earlier and train people to ignore alerts. The 2-consecutive-run rule trades up to one run of delay for far fewer false alarms.
• Paging on test failures alone is usually wrong. Page on impact (deploys blocked, production signal), not on test count.
• What would change my call: a very slow suite (a run takes hours) argues for notifying on the first confirmed failure, since waiting a second run is too costly.

Direct answer

Mean time to detect (MTTD) for regressions is the average, over a set of regressions, of the time between when the bug was introduced and when an automated test first reliably reported it. A regression is something that used to work and broke after a code change.

Precise events:

• Start = the time the first bad commit was merged (found by bisecting, meaning searching commit history in halves for the first commit where the test fails). Not "when someone noticed", and not the time the build ran.
• End = the time of the first confirmed failing automated run for that regression: it failed and failed again on rerun, so a one-off flaky failure does not count.

TTD(regression) = first_confirmed_failure_time - first_bad_commit_merge_time
MTTD = average of TTD over the chosen set of regressions

The formula reads as: each regression contributes its own delay, and MTTD is just their average.

Compute it (per test and overall), then look at the shape

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("""CREATE TABLE regressions (
    regression_id INTEGER PRIMARY KEY,
    test_id TEXT,
    introduced_at TEXT,      -- merge time of the first bad commit (found by bisect)
    first_detected_at TEXT   -- first CONFIRMED failing run (failed, and failed again on rerun)
)""")
rows = [
    (1, "checkout", "2026-03-02 09:00", "2026-03-02 09:40"),
    (2, "checkout", "2026-03-03 14:00", "2026-03-03 14:35"),
    (3, "search",   "2026-03-04 10:00", "2026-03-05 02:00"),  # only the nightly suite covers it
    (4, "search",   "2026-03-05 11:00", "2026-03-06 02:00"),
    (5, "search",   "2026-03-06 16:00", "2026-03-07 02:00"),
    (6, "login",    "2026-03-07 08:00", "2026-03-07 08:25"),
    (7, "login",    "2026-03-08 13:00", "2026-03-08 13:30"),
    (8, "billing",  "2026-03-09 09:00", "2026-03-09 09:50"),
    (9, "billing",  "2026-03-01 12:00", "2026-03-15 02:00"),  # slipped past for two weeks
]
db.executemany("INSERT INTO regressions VALUES (?,?,?,?)", rows)

db.execute("""CREATE VIEW ttd AS
SELECT regression_id, test_id,
       ROUND((julianday(first_detected_at) - julianday(introduced_at)) * 24, 2) AS ttd_hours
FROM regressions""")

print("per test:")
for r in db.execute("SELECT test_id, COUNT(*), ROUND(AVG(ttd_hours),2) FROM ttd GROUP BY test_id ORDER BY test_id"):
    print(*r)

print("overall mean hours:", db.execute("SELECT ROUND(AVG(ttd_hours),2) FROM ttd").fetchone()[0])

# Nearest-rank percentile: the value at position ceil(p * n) in sorted order.
def percentile(p):
    return db.execute("""
        SELECT ttd_hours FROM (
          SELECT ttd_hours, ROW_NUMBER() OVER (ORDER BY ttd_hours) AS rn, COUNT(*) OVER () AS n FROM ttd)
        WHERE rn = CAST((? * n + 0.999999) AS INTEGER)""", (p,)).fetchone()[0]

print("median hours:", percentile(0.5))
print("p90 hours:", percentile(0.9))

Output:

per test:
billing 2 163.41
checkout 2 0.63
login 2 0.46
search 3 13.67
overall mean hours: 41.11
median hours: 0.83
p90 hours: 326.0

Two details of the code. julianday() returns a date as a number of days, so subtracting two of them gives a difference in days, and multiplying by 24 turns it into hours (0.0278 days x 24 = 0.67 hours, the 40 minutes of regression 1). The percentile query picks the value at position ceil(p x n) in sorted order; SQLite has no ceiling function, so adding 0.999999 and casting to an integer (which truncates) does the same job. For p90 with n = 9: 0.9 x 9 = 8.1, plus 0.999999 is 9.099999, which truncates to 9, so it picks the 9th (largest) value. For the median: 0.5 x 9 = 4.5, plus 0.999999 is 5.499999, so the 5th value.

Hand check: regression 1 was merged 09:00 and first confirmed at 09:40, so 40 minutes = 0.67 hours. The nine values sorted are 0.42, 0.5, 0.58, 0.67, 0.83, 10, 15, 16 and 326 hours. They sum to about 370, so the mean is 370 / 9 = 41.1 hours. The median is the 5th value, 0.83 hours. Without the single 326-hour case the mean would be 44 / 8 = 5.5 hours. One number told three different stories.

What makes the number misleading

• Mean versus percentile on skewed data: detection times pile up near zero with a long tail, so the mean is dragged by a few slow cases. Report the median (typical) and a high percentile such as p90 (90 percent of regressions were caught within this time) together. With only 9 points the nearest-rank p90 is simply the maximum, so it needs a larger sample to mean anything.
• Batched runs: the search regressions above took 10 to 16 hours only because that suite runs nightly at 02:00. The number measures the schedule, not test quality. Break MTTD down by suite tier (per-commit, hourly, nightly).
• Flaky tests: counting the first raw failure makes detection look faster than it is (a flake "detects" a bug that is not there yet) or hides a real failure among noise. Require confirmation as in the query above.
• Survivorship (only the cases that "survived" to be measured get counted, so failures are invisible): regressions that no test caught (found by customers) never get an end event, so they vanish from the average. A team that catches only easy bugs quickly gets a great MTTD. Always publish alongside a detection rate (regressions caught by tests divided by all regressions). An escape is a regression that got past the tests and reached customers, so tracking escapes shows what the average leaves out.
• Uncertain start: bisect can be wrong when several commits interact, so treat start times as estimates and audit a sample.
• How an SRE or team would use it: set targets per suite tier on the median and p90, not the mean, for example "median under 1 hour and p90 under 24 hours for the per-commit tier" (illustrative targets to calibrate against your own history), and alarm when the p90 drifts past its target for several weeks running. A nightly-only tier can never meet a 1-hour target, which is a signal to add faster checks, not to blame the tests.
• Gaming: running more frequent trivial tests lowers MTTD without protecting anything. Guard by tracking detection rate and escapes (regressions that reached customers), and by weighting by severity.

Direct answer

Count rows per suite over a rolling 7-day window, keep only rows that gave a verdict (passed or failed; skipped means not run, quarantined means a known-unreliable test moved out of the blocking gate), divide passed by that total, round to two decimals and sort ascending. Use FILTER so a suite whose rows were all skipped or quarantined still appears, and NULLIF so its empty denominator gives NULL instead of a divide-by-zero error.

Setup (illustrative data, timestamps relative to now so results are reproducible)

Run the setup and then each query below in the same psql session against PostgreSQL. It uses a temporary table, so nothing persists.

CREATE TEMP TABLE test_results (
  test_id text, suite text, run_id int, status text,
  started_at timestamptz, finished_at timestamptz);

INSERT INTO test_results
SELECT test_id, suite, run_id, status, started_at, started_at + secs * interval '1 second'
FROM (VALUES
  ('t1', 'checkout', 101, 'passed',      now() - interval '2 days', 4),
  ('t2', 'checkout', 101, 'failed',      now() - interval '2 days', 10),
  ('t2', 'checkout', 101, 'passed',      now() - interval '2 days' + interval '5 minutes', 8),  -- retry
  ('t3', 'checkout', 101, 'passed',      now() - interval '2 days', 6),
  ('t4', 'checkout', 101, 'skipped',     now() - interval '2 days', 0),
  ('t1', 'checkout', 102, 'passed',      now() - interval '1 day', 4),
  ('t2', 'checkout', 102, 'passed',      now() - interval '1 day', 8),
  ('t3', 'checkout', 102, 'failed',      now() - interval '1 day', 10),
  ('t4', 'checkout', 102, 'quarantined', now() - interval '1 day', 2),
  ('s1', 'search',   201, 'passed',      now() - interval '3 days', 2),
  ('s2', 'search',   201, 'passed',      now() - interval '3 days', 2),
  ('s3', 'search',   201, 'failed',      now() - interval '3 days', 12),
  ('s4', 'search',   201, 'passed',      now() - interval '3 days', 2),
  ('s1', 'search',   150, 'failed',      now() - interval '10 days', 2),   -- outside 7 days
  ('l1', 'legacy',   301, 'skipped',     now() - interval '1 day', 0),
  ('l2', 'legacy',   301, 'quarantined', now() - interval '1 day', 0)
) AS v(test_id, suite, run_id, status, started_at, secs);

Plain-language guide to the SQL used

• GROUP BY suite collapses rows into one output row per suite so aggregates like count(*) work per suite.
• count(*) FILTER (WHERE ...) counts only rows matching the condition, so numerator and denominator come from one pass.
• NULLIF(x, 0) returns NULL when x is 0, avoiding divide-by-zero.
• ::numeric converts a value to an exact decimal type so ROUND(..., 1) works.
• EXTRACT(EPOCH FROM interval) turns a time span into a number of seconds.
• WITH name AS (...) is a CTE (common table expression): a named temporary result the next query reads from.
• DISTINCT ON (a, b, c) keeps just the first row for each (a, b, c) group; because of the ORDER BY ... started_at DESC, that first row is the latest attempt.

The query

SELECT suite,
       ROUND(100.0 * count(*) FILTER (WHERE status = 'passed')
             / NULLIF(count(*) FILTER (WHERE status IN ('passed', 'failed')), 0), 2) AS pass_rate_pct
FROM test_results
WHERE started_at >= now() - interval '7 days'
GROUP BY suite
ORDER BY pass_rate_pct ASC NULLS LAST;

Output (psql command tags omitted)

  suite   | pass_rate_pct 
----------+---------------
 checkout |         71.43
 search   |         75.00
 legacy   |              
(3 rows)

checkout: 5 passed of 7 verdicts = 71.43%. search: 3 of 4 = 75.00%; the 10-day-old failure is outside the window. legacy has no verdicts, so its rate is NULL and sorts last.

Data-quality traps that change the numbers

1. Retries. The table has no attempt column, so t2 in run 101 appears twice (failed, then passed on retry) and the query above counts both. The retry variant below keeps only the last attempt per test per run and gives a different answer.
2. Denominators. Putting skipped and quarantined rows in the denominator lowers rates and punishes suites for not running. Also watch an empty denominator, as with legacy.
3. Duplicates. A CI (continuous integration) system that re-sends results double-counts unless there is a unique key such as (run, test, attempt number). This table has no attempt column, so you would add one, or de-duplicate on (run, test, started_at).
4. Window and time zone. A rolling 7 days is not the same as 7 calendar days, and a date cast depends on the session time zone (the time zone setting of your database connection), so 2026-07-01 can be a different instant for two users.
5. In-flight and odd statuses. In-flight rows are tests still running, with a NULL finished_at. Rows still running, error, or differently cased statuses (PASSED) fall outside a strict IN ('passed', 'failed'). Decide their bucket explicitly.
6. Small samples. search has only 4 verdicts, so a single flipped test moves it by 25 points. Show the count next to the percentage.
7. A suite that stops reporting disappears from the output altogether, which looks like health. Left-join from a list of expected suites to catch it, for example SELECT e.suite, r.pass_rate_pct FROM expected_suites e LEFT JOIN rates r USING (suite), where a missing suite shows up with NULL.

Retry variant: last attempt per test per run

WITH final_attempt AS (
  SELECT DISTINCT ON (suite, run_id, test_id) suite, run_id, test_id, status
  FROM test_results
  WHERE started_at >= now() - interval '7 days'
  ORDER BY suite, run_id, test_id, started_at DESC
)
SELECT suite,
       ROUND(100.0 * count(*) FILTER (WHERE status = 'passed')
             / NULLIF(count(*) FILTER (WHERE status IN ('passed', 'failed')), 0), 2) AS pass_rate_pct
FROM final_attempt
GROUP BY suite
ORDER BY pass_rate_pct ASC NULLS LAST;

  suite   | pass_rate_pct 
----------+---------------
 search   |         75.00
 checkout |         83.33
 legacy   |              
(3 rows)

checkout rises from 71.43% to 83.33% (5 of 6). Neither number is wrong; they answer different questions (every attempt versus final outcome), so state which one the dashboard shows.

Variant: failure rate and average duration over 30 days

SELECT suite,
       ROUND(100.0 * count(*) FILTER (WHERE status = 'failed')
             / NULLIF(count(*) FILTER (WHERE status IN ('passed', 'failed')), 0), 2) AS failure_rate_pct,
       ROUND(AVG(EXTRACT(EPOCH FROM finished_at - started_at))::numeric, 1) AS avg_seconds
FROM test_results
WHERE started_at >= now() - interval '30 days'
  AND status IN ('passed', 'failed')
GROUP BY suite
ORDER BY failure_rate_pct DESC;

  suite   | failure_rate_pct | avg_seconds 
----------+------------------+-------------
 search   |            40.00 |         4.0
 checkout |            28.57 |         7.1
(2 rows)

Both rows include the old search failure because 30 days reaches back 10 days. Failures 2 of 5 = 40.00%, durations (2 + 2 + 2 + 12 + 2) / 5 = 4.0 seconds.

Complexity and edge cases

One pass with a filter, O(n) (work grows in proportion to the number of rows in the window), and an index on started_at (a lookup structure so Postgres can jump to recent rows instead of scanning all) lets it skip older data. DISTINCT ON adds a sort, O(n log n) (slightly worse than proportional). Edge cases: no rows in the window, in-flight rows with NULL finished_at (AVG silently skips them, so the average covers fewer rows than the count), and ties in started_at during retries.

Direct answer

Normalise every timestamp to UTC (Coordinated Universal Time) first, then take differences. Define the two metrics explicitly: time to detect is detected_at - created_at (how long a defect or incident existed before someone noticed) and time to restore is resolved_at - detected_at (from noticing to fixed). I use detection as the start of restore time because that is the team's response window; measuring from created_at instead gives total time in production, and I would say which one I picked. Missing values are excluded from the metric that needs them (and counted, never treated as zero), duplicate rows are removed, and overlapping incidents on one service are merged when reporting total downtime.

Terms. ISO 8601 is the standard text format for date-times, such as 2025-03-01T10:00:00+05:30. A UTC offset like +05:30 says how far local clock time is ahead of UTC. A naive timestamp has no offset, so you cannot know which zone it was written in. A CTE (WITH ... AS) in SQL is a named temporary result that the next query can read from.

Approach

1. Parse ISO 8601 timestamps (the standard 2025-03-01T10:00:00+05:30 format that carries its offset) and convert to UTC.
2. Drop exact duplicate ids (re-exports of the same record).
3. Compute both durations per record, skipping records missing a needed timestamp or with a negative duration, and report how many were skipped. In the code below, missing timestamps and negative restore times are listed; a negative time to detect is silently left out of that metric (none occur in the sample), so a production version should list those ids too.
4. For distinct downtime, sort by start per service and merge overlapping windows.

Code (Python standard library, runs as is)

import csv, io, statistics
from datetime import datetime, timezone, timedelta

CSV_TEXT = """id,service,created_at,detected_at,resolved_at
1,api,2025-03-01T10:00:00+00:00,2025-03-01T10:12:00+00:00,2025-03-01T11:00:00+00:00
2,api,2025-03-02T09:00:00-05:00,2025-03-02T09:30:00-05:00,2025-03-02T11:00:00-05:00
3,web,2025-03-03T08:00:00+00:00,,2025-03-03T09:00:00+00:00
4,api,2025-03-01T10:30:00+00:00,2025-03-01T10:40:00+00:00,2025-03-01T11:30:00+00:00
1,api,2025-03-01T10:00:00+00:00,2025-03-01T10:12:00+00:00,2025-03-01T11:00:00+00:00
5,web,2025-03-04T15:00:00+05:30,2025-03-04T15:20:00+05:30,2025-03-04T16:20:00+05:30
6,db,2025-03-05T12:00:00,2025-03-05T12:10:00,2025-03-05T12:50:00
7,db,2025-03-06T12:00:00+00:00,2025-03-06T12:30:00+00:00,2025-03-06T12:10:00+00:00
"""

def parse(ts, naive_flags, row_id):
    if not ts:
        return None
    dt = datetime.fromisoformat(ts)
    if dt.tzinfo is None:                      # no offset given: assume UTC and record that we guessed
        naive_flags.add(row_id)
        dt = dt.replace(tzinfo=timezone.utc)
    return dt.astimezone(timezone.utc)

def load(text):
    seen, rows, notes = set(), [], {"duplicates": 0, "naive_assumed_utc": set(), "bad_order": []}
    for r in csv.DictReader(io.StringIO(text)):
        if r["id"] in seen:                    # exact re-export of the same record
            notes["duplicates"] += 1
            continue
        seen.add(r["id"])
        c, d, s = (parse(r[k], notes["naive_assumed_utc"], r["id"]) for k in ("created_at", "detected_at", "resolved_at"))
        rows.append({"id": r["id"], "service": r["service"], "created": c, "detected": d, "resolved": s})
    return rows, notes

def minutes(a, b):
    return None if a is None or b is None else (b - a).total_seconds() / 60

def metrics(rows, notes):
    ttd, ttr, skipped = [], [], []
    for r in rows:
        d1, d2 = minutes(r["created"], r["detected"]), minutes(r["detected"], r["resolved"])
        if d1 is None or d2 is None:
            skipped.append(r["id"])
        if d1 is not None and d1 >= 0: ttd.append(d1)
        if d2 is not None and d2 < 0: notes["bad_order"].append(r["id"])
        elif d2 is not None: ttr.append(d2)
    return ttd, ttr, skipped

def union_downtime(rows):
    """Minutes of distinct impact per service, merging overlapping created..resolved windows."""
    total = {}
    by_service = {}
    for r in rows:
        if r["created"] and r["resolved"] and r["resolved"] >= r["created"]:
            by_service.setdefault(r["service"], []).append((r["created"], r["resolved"]))
    for svc, spans in by_service.items():
        spans.sort()
        merged = [list(spans[0])]
        for a, b in spans[1:]:
            if a <= merged[-1][1]:
                merged[-1][1] = max(merged[-1][1], b)
            else:
                merged.append([a, b])
        total[svc] = sum((b - a).total_seconds() / 60 for a, b in merged)
    return total

rows, notes = load(CSV_TEXT)
ttd, ttr, skipped = metrics(rows, notes)
print("records kept:", len(rows), "| duplicates dropped:", notes["duplicates"])
print("naive timestamps assumed UTC for ids:", sorted(notes["naive_assumed_utc"]))
print("resolved-before-detected ids excluded from TTR:", notes["bad_order"])
print("ids missing a timestamp (skipped for the metric they need):", skipped)
print("time to detect  (min): n=%d mean=%.1f median=%.1f" % (len(ttd), statistics.mean(ttd), statistics.median(ttd)))
print("time to restore (min): n=%d mean=%.1f median=%.1f" % (len(ttr), statistics.mean(ttr), statistics.median(ttr)))
print("distinct downtime per service (min):", union_downtime(rows))

SQL alternative (SQLite), appended below the code above so it reuses the same data

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE raw (id TEXT, service TEXT, created_at TEXT, detected_at TEXT, resolved_at TEXT)")
db.executemany("INSERT INTO raw VALUES (?,?,?,?,?)",
               [tuple(r.values()) for r in csv.DictReader(io.StringIO(CSV_TEXT))])
# strftime('%s', ...) converts any ISO offset to UTC epoch seconds; NULL or '' stays NULL via NULLIF.
query = """
WITH dedup AS (SELECT DISTINCT * FROM raw),
t AS (
  SELECT id,
         strftime('%s', created_at)              AS c,
         strftime('%s', NULLIF(detected_at, '')) AS d,
         strftime('%s', NULLIF(resolved_at, '')) AS r
  FROM dedup)
SELECT COUNT(d - c)                                        AS n_ttd,
       ROUND(AVG(d - c) / 60.0, 1)                         AS mean_ttd_min,
       COUNT(CASE WHEN r >= d THEN 1 END)                  AS n_ttr,
       ROUND(AVG(CASE WHEN r >= d THEN r - d END) / 60.0, 1) AS mean_ttr_min
FROM t
"""
print(db.execute(query).fetchone())

Output

records kept: 7 | duplicates dropped: 1
naive timestamps assumed UTC for ids: ['6']
resolved-before-detected ids excluded from TTR: ['7']
ids missing a timestamp (skipped for the metric they need): ['3']
time to detect  (min): n=6 mean=18.7 median=16.0
time to restore (min): n=5 mean=57.6 median=50.0
distinct downtime per service (min): {'api': 210.0, 'web': 140.0, 'db': 60.0}
(6, 18.7, 5, 57.6)

Checking the numbers by hand: detect times are 12, 30, 10, 20, 10 and 30 minutes (id 3 has no detection time), so the mean is 112 / 6 = 18.7 and the median is 16. Restore times are 48, 90, 50, 60 and 40 (id 3 is missing a time and id 7 resolved before it was detected, so both are excluded), so 288 / 5 = 57.6. The api incidents 1 and 4 overlap (10:00 to 11:00 and 10:30 to 11:30 UTC), so they merge into 90 minutes, plus 120 for record 2 (09:00 at offset -05:00 is 14:00 UTC): 210.

Why web is 140 and db is 60. Web has two incidents: id 3 (08:00 to 09:00 UTC, 60 minutes, it has no detection time but downtime uses created and resolved) and id 5 (15:00 to 16:20 at +05:30, which is 09:30 to 10:50 UTC, 80 minutes). They do not overlap, so 60 + 80 = 140. Db has id 6 on 5 March (12:00 to 12:50, 50 minutes, naive so assumed UTC) and id 7 on 6 March (12:00 to 12:10 UTC, 10 minutes). They are on different days, so they do not overlap and are not merged: 50 + 10 = 60. Note id 7 is excluded from restore time because it resolved before it was detected, but its created-to-resolved window still counts for downtime.

Reading the SQL. strftime('%s', x) converts a timestamp text into seconds since 1970 in UTC, so subtraction is plain arithmetic. NULLIF(detected_at, '') turns an empty string into NULL, which SQL ignores in averages. COUNT(d - c) counts only rows where the difference is not NULL, so it counts rows that have both timestamps (6 here). SELECT DISTINCT * in the first CTE removes exact duplicate rows. SQLite here is just a convenient in-memory database; the same idea works in any SQL engine, including columnar ones.

Handling the three named problems

Complexity: parsing and durations are O(n); merging is O(n log n) for the sort. Memory is O(n). The SQL version does one pass after deduplication.

Edge cases and pitfalls

• Negative durations (resolved before detected) are data errors, so exclude and list them for repair.
• Report the median next to the mean; one long outage dominates a mean.
• Daylight saving changes create 23 or 25 hour days if you store local time without an offset.
• Naive timestamps are a guess. Ask the source system what zone it writes, and fix it at the source.
• Naming: DORA (dora.dev) now calls its recovery metric failed deployment recovery time, the time to recover from a deployment that fails and needs immediate intervention, measured from the failed deployment rather than from a detection timestamp. Older material calls it MTTR or time to restore service. State which start point your CSV supports before comparing with published benchmarks.