Parcel Delivery Funnel and First-Attempt Rate by City
The last-mile funnel follows each booked shipment through its scans: picked_up, then out_for_delivery, then delivered, with possibly some failed_attempt…
- Difficulty: Hard
- Topics: Conditional Logic, Aggregation, Joins
- Dialect: MySQL
- Problem: #320
Problem statement
The last-mile funnel follows each booked shipment through its scans: picked_up, then out_for_delivery, then delivered, with possibly some failed_attempt scans on the way. A shipment reaches a stage when it has at least one scan of that type (scanned more than once still counts once).
For every destination city, return city, booked (shipments), picked_up, out_for_delivery, delivered (shipments reaching each stage) and first_attempt_pct — delivered shipments with no failed_attempt scan as a percentage of delivered shipments, rounded to 2 decimals, NULL when nothing was delivered. Order by city.
Tables
Table: Shipment
| Column | Type |
|---|---|
| awb | bigint |
| dest_city | varchar |
| booked_on | date |
Primary key: awb.
Table: Scan
| Column | Type |
|---|---|
| scan_id | int |
| awb | bigint |
| scan_type | enum(picked_up, in_hub, out_for_delivery, failed_attempt, delivered) |
| scanned_at | datetime |
Primary key: scan_id.
awb always names a row of Shipment.
Examples
Example 1
Shipment
| awb | dest_city | booked_on |
|---|---|---|
| 600100000001 | Pune | 2024-12-01 |
| 600100000002 | Pune | 2024-12-01 |
| 600100000003 | Pune | 2024-12-02 |
| 600100000004 | Kochi | 2024-12-02 |
| 600100000005 | Kochi | 2024-12-03 |
| 600100000006 | Jaipur | 2024-12-03 |
Scan
| scan_id | awb | scan_type | scanned_at |
|---|---|---|---|
| 1 | 600100000001 | picked_up | 2024-12-01 12:00:00 |
| 2 | 600100000001 | out_for_delivery | 2024-12-02 08:00:00 |
| 3 | 600100000001 | delivered | 2024-12-02 13:00:00 |
| 4 | 600100000002 | picked_up | 2024-12-01 15:00:00 |
| 5 | 600100000002 | out_for_delivery | 2024-12-02 08:30:00 |
| 6 | 600100000002 | failed_attempt | 2024-12-02 17:00:00 |
| 7 | 600100000002 | out_for_delivery | 2024-12-03 08:10:00 |
| 8 | 600100000002 | delivered | 2024-12-03 11:20:00 |
| 9 | 600100000003 | picked_up | 2024-12-02 16:00:00 |
| 10 | 600100000004 | picked_up | 2024-12-02 10:00:00 |
| 11 | 600100000004 | out_for_delivery | 2024-12-04 09:00:00 |
| 12 | 600100000004 | failed_attempt | 2024-12-04 18:00:00 |
Output
| city | booked | picked_up | out_for_delivery | delivered | first_attempt_pct |
|---|---|---|---|---|---|
| Jaipur | 1 | 0 | 0 | 0 | NULL |
| Kochi | 2 | 1 | 1 | 0 | NULL |
| Pune | 3 | 3 | 2 | 2 | 50 |
How to solve Parcel Delivery Funnel and First-Attempt Rate by City
The trap is double counting: a shipment that failed once goes out for delivery twice, so counting out_for_delivery scans per city overstates the funnel. Work in two levels. First, one row per shipment: LEFT JOIN Scan (so a booked shipment with no scans survives) and turn each stage into a 0/1 flag with MAX(CASE WHEN scan_type = '…' THEN 1 ELSE 0 END) — the maximum is 1 if any scan of that type exists.
Second, aggregate the flags per destination city: COUNT(*) is booked, and SUM of each flag counts shipments reaching that stage. The first-attempt numerator is shipments delivered and never failed — a CASE over two flags — and the denominator is delivered shipments, wrapped in NULLIF(…, 0) so a city with no deliveries gets NULL rather than an error. Round to two decimals.
The alternative asks each question with EXISTS subqueries per shipment — no double counting by construction, but five probes into Scan per shipment. The flag table reads the scans once and is the usual way to build a funnel.
Reference solution (MySQL)
WITH per_shipment AS (
SELECT s.awb, s.dest_city,
MAX(CASE WHEN c.scan_type = 'picked_up' THEN 1 ELSE 0 END) AS picked,
MAX(CASE WHEN c.scan_type = 'out_for_delivery' THEN 1 ELSE 0 END) AS ofd,
MAX(CASE WHEN c.scan_type = 'delivered' THEN 1 ELSE 0 END) AS done,
MAX(CASE WHEN c.scan_type = 'failed_attempt' THEN 1 ELSE 0 END) AS failed
FROM Shipment s
LEFT JOIN Scan c ON c.awb = s.awb
GROUP BY s.awb, s.dest_city
)
SELECT dest_city AS city,
COUNT(*) AS booked,
SUM(picked) AS picked_up,
SUM(ofd) AS out_for_delivery,
SUM(done) AS delivered,
ROUND(100 * SUM(CASE WHEN done = 1 AND failed = 0 THEN 1 ELSE 0 END) / NULLIF(SUM(done), 0), 2) AS first_attempt_pct
FROM per_shipment
GROUP BY dest_city
ORDER BY cityAnother way
SELECT s.dest_city AS city,
COUNT(*) AS booked,
SUM(CASE WHEN EXISTS (SELECT 1 FROM Scan c WHERE c.awb = s.awb AND c.scan_type = 'picked_up') THEN 1 ELSE 0 END) AS picked_up,
SUM(CASE WHEN EXISTS (SELECT 1 FROM Scan c WHERE c.awb = s.awb AND c.scan_type = 'out_for_delivery') THEN 1 ELSE 0 END) AS out_for_delivery,
SUM(CASE WHEN EXISTS (SELECT 1 FROM Scan c WHERE c.awb = s.awb AND c.scan_type = 'delivered') THEN 1 ELSE 0 END) AS delivered,
ROUND(100 * SUM(CASE WHEN EXISTS (SELECT 1 FROM Scan c WHERE c.awb = s.awb AND c.scan_type = 'delivered')
AND NOT EXISTS (SELECT 1 FROM Scan c WHERE c.awb = s.awb AND c.scan_type = 'failed_attempt') THEN 1 ELSE 0 END)
/ NULLIF(SUM(CASE WHEN EXISTS (SELECT 1 FROM Scan c WHERE c.awb = s.awb AND c.scan_type = 'delivered') THEN 1 ELSE 0 END), 0), 2) AS first_attempt_pct
FROM Shipment s
GROUP BY s.dest_city
ORDER BY city← Median Pickup-to-Drop Minutes in Each Delivery Zone · Double-Booked Self-Drive Rental Cars →