Median Time to Resolve Incidents by Severity — SQL Problem
Averages hide the shape of MTTR — one 3-day incident drags the mean far from a typical one — so the reliability review reports the median time to resolve.
- Difficulty: Hard
- Topics: Window Functions, Dates, Aggregation
- Dialect: MySQL
- Problem: #347
Problem statement
Averages hide the shape of MTTR — one 3-day incident drags the mean far from a typical one — so the reliability review reports the median time to resolve. An incident's time to resolve is TIMESTAMPDIFF(MINUTE, opened_at, resolved_at); unresolved incidents (resolved_at NULL) are left out. With an even number of incidents the median is the average of the two middle values.
For each severity with at least one resolved incident, return the columns severity, resolved_incidents and median_minutes (rounded to 1 decimal), ordered by severity.
Tables
Table: SevIncident
| Column | Type |
|---|---|
| incident_id | int |
| severity | enum(SEV1, SEV2, SEV3) |
| opened_at | datetime |
| resolved_at | datetime |
Primary key: incident_id.
One row per incident. Times are whole minutes; resolved_at is NULL while the incident is open.
Examples
Example 1
SevIncident
| incident_id | severity | opened_at | resolved_at |
|---|---|---|---|
| 1 | SEV1 | 2025-05-02 10:00:00 | 2025-05-02 10:40:00 |
| 2 | SEV1 | 2025-05-09 22:00:00 | 2025-05-10 01:00:00 |
| 3 | SEV1 | 2025-05-15 03:00:00 | 2025-05-15 03:25:00 |
| 4 | SEV2 | 2025-05-03 12:00:00 | 2025-05-03 13:30:00 |
| 5 | SEV2 | 2025-05-06 09:00:00 | 2025-05-06 10:15:00 |
| 6 | SEV2 | 2025-05-08 14:00:00 | 2025-05-11 14:00:00 |
| 7 | SEV2 | 2025-05-20 16:00:00 | 2025-05-20 17:00:00 |
| 8 | SEV3 | 2025-05-21 11:00:00 | NULL |
Output
| severity | resolved_incidents | median_minutes |
|---|---|---|
| SEV1 | 3 | 40 |
| SEV2 | 4 | 82.5 |
How to solve Median Time to Resolve Incidents by Severity
MySQL has no MEDIAN aggregate, so it is built from window functions. Compute the minutes per resolved incident, then in one pass give each value its position within the severity (ROW_NUMBER() … ORDER BY mins) and the severity's size (COUNT(*) OVER (PARTITION BY severity)). The median sits at positions (n + 1) DIV 2 and (n + 2) DIV 2: for n = 5 both are 3, for n = 4 they are 2 and 3. Keeping the rows at those positions and taking their AVG gives the middle value for odd n and the mean of the two middle values for even n — one formula for both cases. The result is a whole number or ends in .5, so rounding to 1 decimal is exact.
Equal durations do not matter: ROW_NUMBER orders tied values arbitrarily, but they are equal, so the values at the middle positions are the same whichever way the tie was broken. Unresolved incidents are filtered out before numbering and change neither the positions nor n.
The alternative is the counting definition: a value is a middle value when at least half the values are ≤ it and at least half are ≥ it. With duplicates several rows can qualify, so it averages the distinct qualifying values — exactly the one median value, or the two middle ones. It is quadratic per severity; the window version is a sort.
Reference solution (MySQL)
WITH resolved AS (
SELECT severity, TIMESTAMPDIFF(MINUTE, opened_at, resolved_at) AS mins
FROM SevIncident
WHERE resolved_at IS NOT NULL
), ranked AS (
SELECT severity, mins,
ROW_NUMBER() OVER (PARTITION BY severity ORDER BY mins) AS pos,
COUNT(*) OVER (PARTITION BY severity) AS n
FROM resolved
)
SELECT severity, MIN(n) AS resolved_incidents, ROUND(AVG(mins), 1) AS median_minutes
FROM ranked
WHERE pos IN ((n + 1) DIV 2, (n + 2) DIV 2)
GROUP BY severity
ORDER BY severityAnother way
WITH resolved AS (
SELECT incident_id, severity, TIMESTAMPDIFF(MINUTE, opened_at, resolved_at) AS mins
FROM SevIncident WHERE resolved_at IS NOT NULL
), cnt AS (
SELECT severity, COUNT(*) AS n FROM resolved GROUP BY severity
)
SELECT c.severity, c.n AS resolved_incidents, ROUND(AVG(DISTINCT a.mins), 1) AS median_minutes
FROM cnt c
JOIN resolved a ON a.severity = c.severity
WHERE 2 * (SELECT COUNT(*) FROM resolved b WHERE b.severity = a.severity AND b.mins <= a.mins) >= c.n
AND 2 * (SELECT COUNT(*) FROM resolved b WHERE b.severity = a.severity AND b.mins >= a.mins) >= c.n
GROUP BY c.severity, c.n
ORDER BY c.severity← Sessionise API Calls and Count Server Errors · Ready-to-Move 2 BHK Listings Under ₹80 Lakh →