Median Pickup-to-Drop Minutes in Each Delivery Zone

Hard
319.

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. 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

ColumnType
order_idint
zonevarchar
picked_up_atdatetime
delivered_atdatetime

Primary key: order_id.

delivered_at is NULL while the order is still on the way.

Example

The rows must come back in the order shown.

QuickDelivery

order_idzonepicked_up_atdelivered_at
1Andheri2024-11-02 10:00:002024-11-02 10:12:00
2Andheri2024-11-02 10:05:002024-11-02 10:20:00
3Andheri2024-11-02 10:10:002024-11-02 11:40:00
4Bandra2024-11-02 11:00:002024-11-02 11:09:00
5Bandra2024-11-02 11:02:002024-11-02 11:16:00
6Bandra2024-11-02 11:03:00NULL
7Powai2024-11-02 12:00:002024-11-02 12:25:00
8Bandra2024-11-02 11:30:002024-11-02 11:40:00
9Bandra2024-11-02 11:40:002024-11-02 11:51:00

Output

zonedeliveriesmedian_minutes
Andheri315
Bandra410.5
Powai125
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 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

ColumnType
order_idint
zonevarchar
picked_up_atdatetime
delivered_atdatetime

Primary key: order_id.

delivered_at is NULL while the order is still on the way.

Examples

Example 1

QuickDelivery

order_idzonepicked_up_atdelivered_at
1Andheri2024-11-02 10:00:002024-11-02 10:12:00
2Andheri2024-11-02 10:05:002024-11-02 10:20:00
3Andheri2024-11-02 10:10:002024-11-02 11:40:00
4Bandra2024-11-02 11:00:002024-11-02 11:09:00
5Bandra2024-11-02 11:02:002024-11-02 11:16:00
6Bandra2024-11-02 11:03:00NULL
7Powai2024-11-02 12:00:002024-11-02 12:25:00
8Bandra2024-11-02 11:30:002024-11-02 11:40:00
9Bandra2024-11-02 11:40:002024-11-02 11:51:00

Output

zonedeliveriesmedian_minutes
Andheri315
Bandra410.5
Powai125

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 zone

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