Median Pickup-to-Drop Minutes in Each Delivery Zone
Averages are skewed by a few parcels stuck for hours, so the quick-commerce team reports the median delivery time per zone.
- Difficulty: Hard
- Topics: Window Functions, Aggregation, Dates
- Dialect: MySQL
- Problem: #319
Problem statement
Averages are skewed by a few parcels stuck for hours, so the quick-commerce team reports the median delivery time per zone. A delivery's time is the whole minutes from picked_up_at to delivered_at; orders not yet delivered (delivered_at NULL) are ignored. With an even number of deliveries the median is the average of the two middle values.
Return zone, deliveries and median_minutes (rounded to 1 decimal) for every zone with at least one delivered order, ordered by zone.
Tables
Table: QuickDelivery
| Column | Type |
|---|---|
| order_id | int |
| zone | varchar |
| picked_up_at | datetime |
| delivered_at | datetime |
Primary key: order_id.
delivered_at is NULL while the order is still on the way.
Examples
Example 1
QuickDelivery
| order_id | zone | picked_up_at | delivered_at |
|---|---|---|---|
| 1 | Andheri | 2024-11-02 10:00:00 | 2024-11-02 10:12:00 |
| 2 | Andheri | 2024-11-02 10:05:00 | 2024-11-02 10:20:00 |
| 3 | Andheri | 2024-11-02 10:10:00 | 2024-11-02 11:40:00 |
| 4 | Bandra | 2024-11-02 11:00:00 | 2024-11-02 11:09:00 |
| 5 | Bandra | 2024-11-02 11:02:00 | 2024-11-02 11:16:00 |
| 6 | Bandra | 2024-11-02 11:03:00 | NULL |
| 7 | Powai | 2024-11-02 12:00:00 | 2024-11-02 12:25:00 |
| 8 | Bandra | 2024-11-02 11:30:00 | 2024-11-02 11:40:00 |
| 9 | Bandra | 2024-11-02 11:40:00 | 2024-11-02 11:51:00 |
Output
| zone | deliveries | median_minutes |
|---|---|---|
| Andheri | 3 | 15 |
| Bandra | 4 | 10.5 |
| Powai | 1 | 25 |
How to solve Median Pickup-to-Drop Minutes in Each Delivery Zone
SQL has no portable MEDIAN, so build it from ranks. First compute each delivered order's minutes with TIMESTAMPDIFF(MINUTE, picked_up_at, delivered_at) and filter out NULL drops before ranking — otherwise the undelivered orders would shift the middle.
Then, per zone, ROW_NUMBER() OVER (PARTITION BY zone ORDER BY mins) gives positions 1…n and COUNT(*) OVER (PARTITION BY zone) gives n on every row. The middle positions are FLOOR((n+1)/2) and FLOOR(n/2)+1: for n = 5 both are 3, for n = 4 they are 2 and 3. Keeping those rows and averaging them gives the median in both cases; equal times at the middle do not matter, since ROW_NUMBER still picks exactly the right positions. Round to one decimal (a half-minute is the only fraction that can appear).
The window-free alternative uses the definition: the median values are those with at least n/2 values ≤ them and at least n/2 values ≥ them. With duplicates several rows can qualify, so it averages the distinct qualifying values. That is cubic in the worst case; the window version is a sort per zone.
Reference solution (MySQL)
WITH t AS (
SELECT zone, TIMESTAMPDIFF(MINUTE, picked_up_at, delivered_at) AS mins
FROM QuickDelivery
WHERE delivered_at IS NOT NULL
), ranked AS (
SELECT zone, mins,
ROW_NUMBER() OVER (PARTITION BY zone ORDER BY mins) AS rn,
COUNT(*) OVER (PARTITION BY zone) AS cnt
FROM t
)
SELECT zone, MAX(cnt) AS deliveries, ROUND(AVG(mins), 1) AS median_minutes
FROM ranked
WHERE rn IN (FLOOR((cnt + 1) / 2), FLOOR(cnt / 2) + 1)
GROUP BY zone
ORDER BY zoneAnother way
WITH t AS (
SELECT zone, TIMESTAMPDIFF(MINUTE, picked_up_at, delivered_at) AS mins FROM QuickDelivery WHERE delivered_at IS NOT NULL
), c AS (SELECT zone, COUNT(*) AS n FROM t GROUP BY zone)
SELECT c.zone, c.n AS deliveries,
(SELECT ROUND(AVG(DISTINCT a.mins), 1) FROM t a WHERE a.zone = c.zone
AND (SELECT COUNT(*) FROM t b WHERE b.zone = a.zone AND b.mins <= a.mins) >= c.n / 2
AND (SELECT COUNT(*) FROM t b WHERE b.zone = a.zone AND b.mins >= a.mins) >= c.n / 2) AS median_minutes
FROM c ORDER BY c.zone← The Day Each Driver Hit the Monthly ₹10,000 Target · Parcel Delivery Funnel and First-Attempt Rate by City →