Parcel Delivery Funnel and First-Attempt Rate by City

Hard
320.

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

ColumnType
awbbigint
dest_cityvarchar
booked_ondate

Primary key: awb.

Table: Scan

ColumnTypeValues
scan_idint
awbbigint
scan_typeenumpicked_up, in_hub, out_for_delivery, failed_attempt, delivered
scanned_atdatetime

Primary key: scan_id.

awb always names a row of Shipment.

Example

The rows must come back in the order shown.

Shipment

awbdest_citybooked_on
600100000001Pune2024-12-01
600100000002Pune2024-12-01
600100000003Pune2024-12-02
600100000004Kochi2024-12-02
600100000005Kochi2024-12-03
600100000006Jaipur2024-12-03

Scan

scan_idawbscan_typescanned_at
1600100000001picked_up2024-12-01 12:00:00
2600100000001out_for_delivery2024-12-02 08:00:00
3600100000001delivered2024-12-02 13:00:00
4600100000002picked_up2024-12-01 15:00:00
5600100000002out_for_delivery2024-12-02 08:30:00
6600100000002failed_attempt2024-12-02 17:00:00
7600100000002out_for_delivery2024-12-03 08:10:00
8600100000002delivered2024-12-03 11:20:00
9600100000003picked_up2024-12-02 16:00:00
10600100000004picked_up2024-12-02 10:00:00
2 more rows

Output

citybookedpicked_upout_for_deliverydeliveredfirst_attempt_pct
Jaipur1000NULL
Kochi2110NULL
Pune332250
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.

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

ColumnType
awbbigint
dest_cityvarchar
booked_ondate

Primary key: awb.

Table: Scan

ColumnType
scan_idint
awbbigint
scan_typeenum(picked_up, in_hub, out_for_delivery, failed_attempt, delivered)
scanned_atdatetime

Primary key: scan_id.

awb always names a row of Shipment.

Examples

Example 1

Shipment

awbdest_citybooked_on
600100000001Pune2024-12-01
600100000002Pune2024-12-01
600100000003Pune2024-12-02
600100000004Kochi2024-12-02
600100000005Kochi2024-12-03
600100000006Jaipur2024-12-03

Scan

scan_idawbscan_typescanned_at
1600100000001picked_up2024-12-01 12:00:00
2600100000001out_for_delivery2024-12-02 08:00:00
3600100000001delivered2024-12-02 13:00:00
4600100000002picked_up2024-12-01 15:00:00
5600100000002out_for_delivery2024-12-02 08:30:00
6600100000002failed_attempt2024-12-02 17:00:00
7600100000002out_for_delivery2024-12-03 08:10:00
8600100000002delivered2024-12-03 11:20:00
9600100000003picked_up2024-12-02 16:00:00
10600100000004picked_up2024-12-02 10:00:00
11600100000004out_for_delivery2024-12-04 09:00:00
12600100000004failed_attempt2024-12-04 18:00:00

Output

citybookedpicked_upout_for_deliverydeliveredfirst_attempt_pct
Jaipur1000NULL
Kochi2110NULL
Pune332250

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 city

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