Median Days to Close Service Requests by Department
Averages hide the long tail of the corporation's service requests, so the commissioner's dashboard shows the median number of days a department takes to…
- Difficulty: Hard
- Topics: Window Functions, Aggregation, Dates
- Dialect: MySQL
- Problem: #94
Problem statement
Averages hide the long tail of the corporation's service requests, so the commissioner's dashboard shows the median number of days a department takes to close a request, DATEDIFF(closed_on, opened_on). With an even number of closed requests the median is the average of the two middle values. Requests still open (closed_on NULL) are not counted.
For each department with at least one closed request, return department, closed_requests and median_days rounded to one decimal, ordered by department.
Tables
Table: service_requests
| Column | Type |
|---|---|
| request_id | int |
| department | varchar |
| opened_on | date |
| closed_on | date |
Primary key: request_id.
One row per citizen service request; closed_on is NULL while it is open and never before opened_on.
Examples
Example 1
service_requests
| request_id | department | opened_on | closed_on |
|---|---|---|---|
| 1 | Roads | 2024-04-01 | 2024-04-03 |
| 2 | Roads | 2024-04-02 | 2024-04-12 |
| 3 | Roads | 2024-04-02 | 2024-04-07 |
| 4 | Roads | 2024-04-05 | NULL |
| 5 | Sanitation | 2024-04-01 | 2024-04-02 |
| 6 | Sanitation | 2024-04-03 | 2024-04-07 |
| 7 | Sanitation | 2024-04-03 | 2024-04-30 |
| 8 | Sanitation | 2024-04-06 | 2024-04-09 |
| 9 | Water Supply | 2024-04-04 | 2024-04-11 |
| 10 | Electricity | 2024-04-04 | NULL |
Output
| department | closed_requests | median_days |
|---|---|---|
| Roads | 3 | 5 |
| Sanitation | 4 | 3.5 |
| Water Supply | 1 | 7 |
How to solve Median Days to Close Service Requests by Department
MySQL has no MEDIAN(), so the median is built from a sort. Number each department's closing times with ROW_NUMBER() OVER (PARTITION BY department ORDER BY days) and attach the department size n with COUNT(*) OVER. The middle positions are (n + 1) DIV 2 and (n + 2) DIV 2: for n = 3 both are 2, for n = 4 they are 2 and 3. Keeping those rows and averaging them gives the median in both cases. Equal values may be numbered in either order, but they are equal, so the picked values — and the median — do not change.
In the example Roads has 2, 5 and 10 days (median 5; the open request is ignored) and Sanitation 1, 3, 4 and 27 days (median 3.5) — the 27-day outlier would drag a mean to 8.75, which is why the dashboard wants the median.
The alternative uses the definition instead of positions: a value is a median candidate when at least half the values are at or below it and at least half at or above it. Only the middle value (odd n) or the two middle values (even n) qualify, so the average of the distinct candidates is the median. It is quadratic per department; the window version is one sort.
Reference solution (MySQL)
WITH closed AS (
SELECT department, DATEDIFF(closed_on, opened_on) AS days
FROM service_requests
WHERE closed_on IS NOT NULL
),
ranked AS (
SELECT department, days,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY days) AS rn,
COUNT(*) OVER (PARTITION BY department) AS n
FROM closed
)
SELECT department, MAX(n) AS closed_requests, ROUND(AVG(days), 1) AS median_days
FROM ranked
WHERE rn IN ((n + 1) DIV 2, (n + 2) DIV 2)
GROUP BY department
ORDER BY departmentAnother way
WITH closed AS (SELECT department, DATEDIFF(closed_on, opened_on) AS days FROM service_requests WHERE closed_on IS NOT NULL),
sizes AS (SELECT department, COUNT(*) AS n FROM closed GROUP BY department)
SELECT a.department, s.n AS closed_requests, ROUND(AVG(DISTINCT a.days), 1) AS median_days
FROM closed a
JOIN sizes s ON s.department = a.department
WHERE (SELECT COUNT(*) FROM closed x WHERE x.department = a.department AND x.days <= a.days) * 2 >= s.n
AND (SELECT COUNT(*) FROM closed x WHERE x.department = a.department AND x.days >= a.days) * 2 >= s.n
GROUP BY a.department, s.n
ORDER BY a.department← First Day a Member's Fine Balance Reached ₹500 · Donor Retention by First-Gift Year →