SQL interview question: median delivery time by group
Problem
Shared data rulesFor each city and time of day an order was placed, count orders delivered in September 2026. Also find the median time from order to delivery in minutes.
Formulas
time from order to delivery = delivered_at − created_atin minutes, with seconds: 25 min 30 s = 25.5daypart: 'night' — hours 0–5, 'morning' — 6–11, 'afternoon' — 12–17, 'evening' — 18–23 (hour of created_at)
Example
orders| order_id | city | created_at |
|---|---|---|
| 401 | Boston | 2026-09-04 19:50 |
| 402 | Boston | 2026-09-05 18:30 |
| 403 | Boston | 2026-09-06 09:00 |
deliveries| order_id | delivered_at |
|---|---|
| 401 | 2026-09-04 20:20 |
| 402 | 2026-09-05 19:10 |
| 403 | 2026-09-06 09:25:30 |
| city | daypart | deliveries | median_minutes |
|---|---|---|---|
| Boston | evening | 2 | 35 |
| Boston | morning | 1 | 25.5 |
Evening: two deliveries, 30 and 40 minutes, so the median is (30 + 40) / 2 = 35. Morning: one delivery of 25 minutes 30 seconds — 25.5.
Solve it free
Your query runs on PostgreSQL in your browser. No sign-up needed.
Skills it tests
More interview questions
- SQL: Pivot monthly category revenueMedium · Pivot and unpivot
- SQL: Category pairs bought togetherMedium · Pairs and self-joins
- SQL: Recursive employee hierarchyMedium · Hierarchies and recursion
- SQL: Customers who bought every categoryMedium · Relational division
- SQL: A/B test conversion rate by platformMedium · A/B tests