Median Completed Call Duration per Tower — SQL Problem
Average call length is skewed by a few marathon calls, so the capacity team reports the median instead.
- Difficulty: Hard
- Topics: Window Functions, Aggregation
- Dialect: MySQL
- Problem: #518
Problem statement
Average call length is skewed by a few marathon calls, so the capacity team reports the median instead. Only calls that ended normally count; dropped calls are left out.
For every tower with at least one normally ended call, return tower_id, completed_calls (how many such calls) and median_duration_sec: the middle duration when the count is odd, or the average of the two middle durations when it is even, rounded to 1 decimal. Order by tower_id.
Tables
Table: CallRecord
| Column | Type |
|---|---|
| call_id | int |
| tower_id | int |
| duration_sec | int |
| end_reason | enum(normal, dropped) |
Primary key: call_id.
One voice call carried by a tower and how it ended.
Examples
Example 1
CallRecord
| call_id | tower_id | duration_sec | end_reason |
|---|---|---|---|
| 1 | 101 | 30 | normal |
| 2 | 101 | 120 | normal |
| 3 | 101 | 600 | dropped |
| 4 | 101 | 45 | normal |
| 5 | 101 | 300 | normal |
| 6 | 102 | 60 | normal |
| 7 | 102 | 90 | normal |
| 8 | 102 | 60 | normal |
| 9 | 103 | 200 | normal |
| 10 | 104 | 75 | dropped |
Output
| tower_id | completed_calls | median_duration_sec |
|---|---|---|
| 101 | 4 | 82.5 |
| 102 | 3 | 60 |
| 103 | 1 | 200 |
How to solve Median Completed Call Duration per Tower
MySQL has no MEDIAN aggregate, so the median is built from positions. After dropping the dropped calls, ROW_NUMBER() orders each tower's durations (with call_id breaking equal durations, so every row has a unique position) and COUNT(*) OVER (PARTITION BY tower_id) puts the tower's count on every row.
For n rows the middle positions are FLOOR((n + 1) / 2) and FLOOR((n + 2) / 2): for n = 4 that is 2 and 3, for n = 5 both are 3. Keeping the rows at those positions and averaging them yields the median either way — one row averaged is itself. Equal durations do not disturb this: tied values sit next to each other in the order, and whichever of them lands in the middle has the same value. The result is rounded to one decimal, and halves such as 82.5 are exact.
rn BETWEEN n/2 AND n/2 + 1 selects the same middle rows with decimal division. Without window functions, a correlated count of the calls that sort before each call gives its position — correct but quadratic per tower. The window version costs one sort per tower.
Reference solution (MySQL)
WITH ranked AS (
SELECT tower_id, duration_sec,
ROW_NUMBER() OVER (PARTITION BY tower_id ORDER BY duration_sec, call_id) AS rn,
COUNT(*) OVER (PARTITION BY tower_id) AS cnt
FROM CallRecord
WHERE end_reason = 'normal'
)
SELECT tower_id, MAX(cnt) AS completed_calls, ROUND(AVG(duration_sec), 1) AS median_duration_sec
FROM ranked
WHERE rn IN (FLOOR((cnt + 1) / 2), FLOOR((cnt + 2) / 2))
GROUP BY tower_id
ORDER BY tower_idAnother way
WITH ranked AS (
SELECT tower_id, duration_sec,
ROW_NUMBER() OVER (PARTITION BY tower_id ORDER BY duration_sec, call_id) AS rn,
COUNT(*) OVER (PARTITION BY tower_id) AS cnt
FROM CallRecord WHERE end_reason = 'normal'
)
SELECT tower_id, MIN(cnt) AS completed_calls, ROUND(SUM(duration_sec) / COUNT(*), 1) AS median_duration_sec
FROM ranked
WHERE rn BETWEEN cnt / 2 AND cnt / 2 + 1
GROUP BY tower_id
ORDER BY tower_idAnother way
WITH c AS (SELECT call_id, tower_id, duration_sec FROM CallRecord WHERE end_reason = 'normal'),
pos AS (
SELECT a.tower_id, a.duration_sec,
(SELECT COUNT(*) FROM c b WHERE b.tower_id = a.tower_id
AND (b.duration_sec < a.duration_sec OR (b.duration_sec = a.duration_sec AND b.call_id <= a.call_id))) AS rn,
(SELECT COUNT(*) FROM c b WHERE b.tower_id = a.tower_id) AS cnt
FROM c a
)
SELECT tower_id, MAX(cnt) AS completed_calls, ROUND(AVG(duration_sec), 1) AS median_duration_sec
FROM pos WHERE 2 * rn IN (cnt, cnt + 1, cnt + 2)
GROUP BY tower_id ORDER BY tower_id← Feeder Downtime After Merging Overlapping Outages · Prepaid Recharge Next-Month Retention by Cohort →