Median Completed Call Duration per Tower

Hard
518.

Median Completed Call Duration per Tower

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

ColumnTypeValues
call_idint
tower_idint
duration_secint
end_reasonenumnormal, dropped

Primary key: call_id.

One voice call carried by a tower and how it ended.

Example

The rows must come back in the order shown.

CallRecord

call_idtower_idduration_secend_reason
110130normal
2101120normal
3101600dropped
410145normal
5101300normal
610260normal
710290normal
810260normal
9103200normal
1010475dropped

Output

tower_idcompleted_callsmedian_duration_sec
101482.5
102360
1031200
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 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

ColumnType
call_idint
tower_idint
duration_secint
end_reasonenum(normal, dropped)

Primary key: call_id.

One voice call carried by a tower and how it ended.

Examples

Example 1

CallRecord

call_idtower_idduration_secend_reason
110130normal
2101120normal
3101600dropped
410145normal
5101300normal
610260normal
710290normal
810260normal
9103200normal
1010475dropped

Output

tower_idcompleted_callsmedian_duration_sec
101482.5
102360
1031200

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_id

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

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