Median Delivery Time per Hub

A courier company wants a delivery-time figure per hub that is not dragged around by a few very late orders, so it asks for the median of minutes for each hub.

Sort a hub's delivery times. With an odd number of deliveries, the median is the middle value. With an even number, it is the average of the two middle values. Repeated values count individually.

Return hub and median_minutes, rounded to 1 decimal place, one row per hub that has deliveries, ordered by hub ascending. SQLite has no built-in MEDIAN function. If the table is empty, return no rows.

Tables

courier_deliveries — One row per completed delivery.

Example

courier_deliveries

Expected output

Return the rows in exactly the order described. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Window Functions · problem 43 of 70 · SQLite

The idea

A median needs two facts per row: its position in the sorted group and the group size. ROW_NUMBER() OVER (PARTITION BY hub ORDER BY minutes) gives the position, and COUNT(*) OVER (PARTITION BY hub) gives the size on every row.

With integer division, (n + 1) / 2 and (n + 2) / 2 are equal when n is odd (the single middle row) and adjacent when n is even (the two middle rows). Keep the rows at those positions and average them per hub. AVG always returns a real number, so an even pair such as 10 and 21 gives 15.5 rather than a truncated 15. Duplicate values are fine because ROW_NUMBER gives each copy its own position.

The alternative numbers each hub's rows from both ends. A row is in the middle when the two numbers differ by at most 1. Both orderings use delivery_id as a tie-breaker so that equal minutes get mirror-image positions.

Solution

WITH ordered AS (
  SELECT
    hub,
    minutes,
    ROW_NUMBER() OVER (PARTITION BY hub ORDER BY minutes) AS pos,
    COUNT(*)     OVER (PARTITION BY hub)                  AS n
  FROM courier_deliveries
)
SELECT hub, ROUND(AVG(minutes), 1) AS median_minutes
FROM ordered
WHERE pos IN ((n + 1) / 2, (n + 2) / 2)
GROUP BY hub
ORDER BY hub;

Another way

WITH ordered AS (
  SELECT
    hub,
    minutes,
    ROW_NUMBER() OVER (PARTITION BY hub ORDER BY minutes, delivery_id)           AS up,
    ROW_NUMBER() OVER (PARTITION BY hub ORDER BY minutes DESC, delivery_id DESC) AS down
  FROM courier_deliveries
)
SELECT hub, ROUND(AVG(minutes), 1) AS median_minutes
FROM ordered
WHERE ABS(up - down) <= 1
GROUP BY hub
ORDER BY hub;

Why common answers fail

Computes the mean, which a single very late delivery pulls upward; the median ignores how extreme the outliers are.

SELECT hub, ROUND(AVG(minutes), 1) AS median_minutes
FROM courier_deliveries GROUP BY hub ORDER BY hub;

Takes only the lower middle value, so hubs with an even number of deliveries get the wrong median.

WITH ordered AS (
  SELECT hub, minutes,
         ROW_NUMBER() OVER (PARTITION BY hub ORDER BY minutes) AS pos,
         COUNT(*) OVER (PARTITION BY hub) AS n
  FROM courier_deliveries
)
SELECT hub, ROUND(minutes, 1) AS median_minutes
FROM ordered WHERE pos = (n + 1) / 2 ORDER BY hub;

Divides two integers, so SQLite truncates and an even-count median such as 15.5 becomes 15.

WITH ordered AS (
  SELECT hub, minutes,
         ROW_NUMBER() OVER (PARTITION BY hub ORDER BY minutes) AS pos,
         COUNT(*) OVER (PARTITION BY hub) AS n
  FROM courier_deliveries
)
SELECT hub, ROUND(SUM(minutes) / COUNT(*), 1) AS median_minutes
FROM ordered WHERE pos IN ((n + 1) / 2, (n + 2) / 2)
GROUP BY hub ORDER BY hub;

What the interviewer is checking

Medians are a common test of window-function fluency in engines without a MEDIAN function, with odd/even counts and integer division as the traps.

Mistakes to avoid

• Using AVG over the whole group, which is the mean, not the median.
• Handling only odd group sizes.
• Integer division when averaging the two middle values.
• Ascending and descending row numbers without a consistent tie-breaker.

Follow-ups to expect

• Compute the 90th percentile time per hub (keep the row at position CEIL(0.9 * n)).
• Median per hub per weekday (add the weekday to PARTITION BY and GROUP BY).
• Flag deliveries slower than twice their hub's median (join the medians back to the deliveries).

In real work

Median delivery, response and page-load times are preferred over means on operations dashboards because a few extreme cases do not distort them.

Practice next