Median Delivery Days per Courier Partner — SQL Problem
The marketplace ships through several courier partners and renegotiates contracts on their median delivery time, which a few lost parcels cannot distort the…
- Difficulty: Hard
- Topics: Window Functions, Aggregation, Dates
- Dialect: MySQL
- Problem: #118
Problem statement
The marketplace ships through several courier partners and renegotiates contracts on their median delivery time, which a few lost parcels cannot distort the way they distort an average. A shipment's delivery time is the days from shipped_on to delivered_on; shipments not delivered yet are ignored.
For every courier with at least one delivered shipment return courier, deliveries and median_days — the middle value, or the average of the two middle values for an even count, rounded to 1 decimal. Order by median_days, then courier.
Tables
Table: courier_shipments
| Column | Type |
|---|---|
| shipment_id | int |
| courier | varchar |
| shipped_on | date |
| delivered_on | date |
Primary key: shipment_id.
One row per shipment handed to a courier; delivered_on is NULL while in transit.
Examples
Example 1
courier_shipments
| shipment_id | courier | shipped_on | delivered_on |
|---|---|---|---|
| 1 | Delhivery | 2024-08-01 | 2024-08-03 |
| 2 | Delhivery | 2024-08-01 | 2024-08-09 |
| 3 | Delhivery | 2024-08-02 | 2024-08-05 |
| 4 | Ekart | 2024-08-02 | 2024-08-04 |
| 5 | Ekart | 2024-08-03 | 2024-08-04 |
| 6 | Ekart | 2024-08-03 | 2024-08-07 |
| 7 | Ekart | 2024-08-04 | 2024-08-07 |
| 8 | Blue Dart | 2024-08-04 | 2024-08-06 |
| 9 | Blue Dart | 2024-08-05 | NULL |
Output
| courier | deliveries | median_days |
|---|---|---|
| Blue Dart | 1 | 2 |
| Ekart | 4 | 2.5 |
| Delhivery | 3 | 3 |
How to solve Median Delivery Days per Courier Partner
MySQL has no MEDIAN aggregate, so the median is built from positions. After computing each delivered shipment's days, number each courier's rows in order of days with ROW_NUMBER() (the shipment_id tie-breaker keeps the numbering stable) and attach the courier's row count n with COUNT(*) OVER (PARTITION BY courier).
The middle rows sit at positions FLOOR((n + 1) / 2) and FLOOR(n / 2) + 1. For odd n both are the same position, and AVG over one row is that value; for even n they are the two middle rows and AVG gives their mean. Ekart in the example has days 1, 2, 3, 4 — positions 2 and 3, median 2.5. Ties in days do not matter: equal values are interchangeable in the sorted list.
The alternative numbers the rows both upwards and downwards; the middle rows are those where the two numbers differ by at most 1. For that to work the two orders must be exact mirrors, hence days DESC, shipment_id DESC. (Write the test as up + 1 >= down AND down + 1 >= up rather than ABS(up - down) <= 1: MySQL types ROW_NUMBER as an unsigned integer, and subtracting a larger one from a smaller one is an out-of-range error rather than a negative number.) Both cost one sort per courier. Round only the final value so the half-day medians print as 2.5 everywhere.
Reference solution (MySQL)
WITH d AS (
SELECT courier, shipment_id, DATEDIFF(delivered_on, shipped_on) AS days
FROM courier_shipments
WHERE delivered_on IS NOT NULL
), r AS (
SELECT courier, days,
ROW_NUMBER() OVER (PARTITION BY courier ORDER BY days, shipment_id) AS rn,
COUNT(*) OVER (PARTITION BY courier) AS n
FROM d
)
SELECT courier, MAX(n) AS deliveries, ROUND(AVG(days), 1) AS median_days
FROM r
WHERE rn IN (FLOOR((n + 1) / 2), FLOOR(n / 2) + 1)
GROUP BY courier
ORDER BY median_days, courierAnother way
WITH d AS (
SELECT courier, shipment_id, DATEDIFF(delivered_on, shipped_on) AS days FROM courier_shipments WHERE delivered_on IS NOT NULL
), r AS (
SELECT courier, days,
ROW_NUMBER() OVER (PARTITION BY courier ORDER BY days, shipment_id) AS up,
ROW_NUMBER() OVER (PARTITION BY courier ORDER BY days DESC, shipment_id DESC) AS down
FROM d
)
SELECT courier, (SELECT COUNT(*) FROM d d2 WHERE d2.courier = r.courier) AS deliveries, ROUND(AVG(days), 1) AS median_days
FROM r
WHERE up + 1 >= down AND down + 1 >= up
GROUP BY courier
ORDER BY median_days, courier← Monthly Buyer Cohort Retention · Top Three Sellers per Category in Q4, With Ties →