Median Time to Resolve Incidents by Severity

Hard
347.

Median Time to Resolve Incidents by Severity

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

ColumnTypeValues
incident_idint
severityenumSEV1, SEV2, SEV3
opened_atdatetime
resolved_atdatetime

Primary key: incident_id.

One row per incident. Times are whole minutes; resolved_at is NULL while the incident is open.

Example

The rows must come back in the order shown.

SevIncident

incident_idseverityopened_atresolved_at
1SEV12025-05-02 10:00:002025-05-02 10:40:00
2SEV12025-05-09 22:00:002025-05-10 01:00:00
3SEV12025-05-15 03:00:002025-05-15 03:25:00
4SEV22025-05-03 12:00:002025-05-03 13:30:00
5SEV22025-05-06 09:00:002025-05-06 10:15:00
6SEV22025-05-08 14:00:002025-05-11 14:00:00
7SEV22025-05-20 16:00:002025-05-20 17:00:00
8SEV32025-05-21 11:00:00NULL

Output

severityresolved_incidentsmedian_minutes
SEV1340
SEV2482.5
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.

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

ColumnType
incident_idint
severityenum(SEV1, SEV2, SEV3)
opened_atdatetime
resolved_atdatetime

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_idseverityopened_atresolved_at
1SEV12025-05-02 10:00:002025-05-02 10:40:00
2SEV12025-05-09 22:00:002025-05-10 01:00:00
3SEV12025-05-15 03:00:002025-05-15 03:25:00
4SEV22025-05-03 12:00:002025-05-03 13:30:00
5SEV22025-05-06 09:00:002025-05-06 10:15:00
6SEV22025-05-08 14:00:002025-05-11 14:00:00
7SEV22025-05-20 16:00:002025-05-20 17:00:00
8SEV32025-05-21 11:00:00NULL

Output

severityresolved_incidentsmedian_minutes
SEV1340
SEV2482.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 severity

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