Median Delivery Days per Courier Partner

Hard
118.

Median Delivery Days per Courier Partner

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

ColumnType
shipment_idint
couriervarchar
shipped_ondate
delivered_ondate

Primary key: shipment_id.

One row per shipment handed to a courier; delivered_on is NULL while in transit.

Example

The rows must come back in the order shown.

courier_shipments

shipment_idcouriershipped_ondelivered_on
1Delhivery2024-08-012024-08-03
2Delhivery2024-08-012024-08-09
3Delhivery2024-08-022024-08-05
4Ekart2024-08-022024-08-04
5Ekart2024-08-032024-08-04
6Ekart2024-08-032024-08-07
7Ekart2024-08-042024-08-07
8Blue Dart2024-08-042024-08-06
9Blue Dart2024-08-05NULL

Output

courierdeliveriesmedian_days
Blue Dart12
Ekart42.5
Delhivery33
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 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

ColumnType
shipment_idint
couriervarchar
shipped_ondate
delivered_ondate

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_idcouriershipped_ondelivered_on
1Delhivery2024-08-012024-08-03
2Delhivery2024-08-012024-08-09
3Delhivery2024-08-022024-08-05
4Ekart2024-08-022024-08-04
5Ekart2024-08-032024-08-04
6Ekart2024-08-032024-08-07
7Ekart2024-08-042024-08-07
8Blue Dart2024-08-042024-08-06
9Blue Dart2024-08-05NULL

Output

courierdeliveriesmedian_days
Blue Dart12
Ekart42.5
Delhivery33

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

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