Median Days to Close Service Requests by Department

Hard
94.

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

ColumnType
request_idint
departmentvarchar
opened_ondate
closed_ondate

Primary key: request_id.

One row per citizen service request; closed_on is NULL while it is open and never before opened_on.

Example

The rows must come back in the order shown.

service_requests

request_iddepartmentopened_onclosed_on
1Roads2024-04-012024-04-03
2Roads2024-04-022024-04-12
3Roads2024-04-022024-04-07
4Roads2024-04-05NULL
5Sanitation2024-04-012024-04-02
6Sanitation2024-04-032024-04-07
7Sanitation2024-04-032024-04-30
8Sanitation2024-04-062024-04-09
9Water Supply2024-04-042024-04-11
10Electricity2024-04-04NULL

Output

departmentclosed_requestsmedian_days
Roads35
Sanitation43.5
Water Supply17
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 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

ColumnType
request_idint
departmentvarchar
opened_ondate
closed_ondate

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_iddepartmentopened_onclosed_on
1Roads2024-04-012024-04-03
2Roads2024-04-022024-04-12
3Roads2024-04-022024-04-07
4Roads2024-04-05NULL
5Sanitation2024-04-012024-04-02
6Sanitation2024-04-032024-04-07
7Sanitation2024-04-032024-04-30
8Sanitation2024-04-062024-04-09
9Water Supply2024-04-042024-04-11
10Electricity2024-04-04NULL

Output

departmentclosed_requestsmedian_days
Roads35
Sanitation43.5
Water Supply17

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 department

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