deliveries(delivery_id, city, promised_at, delivered_at)

For each city, return the median and 90th percentile delivery delay in whole minutes, where delay is delivered_at minus promised_at and is negative for early deliveries.

The median is the middle value, or the average of the two middle values when the count is even, so return p50_delay_min rounded to two decimals. p90_delay_min uses the nearest-rank definition: the value at position CEIL(0.9 * n) in ascending order, a whole number of minutes.

Return city, p50_delay_min and p90_delay_min, ordered by city.

Negative delays are the part that breaks careless solutions: sorting or averaging on an absolute value turns an early delivery into a late one.

Tables

deliveries

delivery_id  city    promised_at          delivered_at
-----------  ------  -------------------  -------------------
1            Mumbai  2026-04-01 12:00:00  2026-04-01 11:30:00
2            Mumbai  2026-04-01 12:00:00  2026-04-01 11:50:00
3            Mumbai  2026-04-01 12:00:00  2026-04-01 12:05:00
4            Mumbai  2026-04-01 12:00:00  2026-04-01 13:00:00
5            Pune    2026-04-01 09:00:00  2026-04-01 08:45:00
6            Delhi   2026-04-01 09:00:00  2026-04-01 09:20:00
7            Delhi   2026-04-01 10:00:00  2026-04-01 10:40:00
8            Delhi   2026-04-01 11:00:00  2026-04-01 11:01:00

Expected result

city    p50_delay_min  p90_delay_min
------  -------------  -------------
Delhi   20.00          40
Mumbai  -2.50          60
Pune    -15.00         -15