Write SQL and Python, run instantly in your browser, and track your progress.
You are a Fulfillment Operations Analyst at Amazon. The team needs to identify the most recent delivered shipment for each completed purchase to investigate delivery performance. Your task is to provide, per purchase, the latest delivered shipment including its delivery date, service level, carrier, and a rank indicating recency. Exclude shipments that were not delivered or lack a delivery date. Sort by the smallest purchase identifier; break remaining ties by the smallest shipment identifier.
| Column Name | Type |
|---|---|
| shipment_id | INTEGER |
| order_id | INTEGER |
| warehouse_code | TEXT |
You are a Fulfillment Operations Analyst at Amazon. The team needs to identify the most recent delivered shipment for each completed purchase to investigate delivery performance. Your task is to provide, per purchase, the latest delivered shipment including its delivery date, service level, carrier, and a rank indicating recency. Exclude shipments that were not delivered or lack a delivery date. Sort by the smallest purchase identifier; break remaining ties by the smallest shipment identifier.
| Column Name | Type |
|---|---|
| shipment_id | INTEGER |
| order_id | INTEGER |
| warehouse_code | TEXT |
| carrier_name | TEXT |
| carrier_name | TEXT |
| service_level | TEXT |
| service_level | TEXT |
| tracking_number | TEXT |
| tracking_number | TEXT |
| ship_date | TEXT |
| ship_date | TEXT |
| est_delivery_date | TEXT |
| est_delivery_date | TEXT |
| delivery_date | TEXT |
| delivery_date | TEXT |
| status | TEXT |
| status | TEXT |
| weight_kg | REAL |
| weight_kg | REAL |
| shipping_fee_charged | REAL |
| shipping_fee_charged | REAL |
| shipment_id | order_id | warehouse_code | carrier_name | service_level | tracking_number | ship_date | est_delivery_date | delivery_date | status | weight_kg | shipping_fee_charged |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 2 |
| shipment_id | order_id | warehouse_code | carrier_name | service_level | tracking_number | ship_date | est_delivery_date | delivery_date | status | weight_kg | shipping_fee_charged |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 2 |
| order_id | shipment_id | delivery_date | service_level | carrier_name | shipment_rank |
|---|---|---|---|---|---|
| 2 | 1 | 2025-03-05 | economy | UPS | 1 |
| 6 | 5 | 2025-04-12 | standard | UPS | 1 |
| 10 | 8 |
| order_id | shipment_id | delivery_date | service_level | carrier_name | shipment_rank |
|---|---|---|---|---|---|
| 2 | 1 | 2025-03-05 | economy | UPS | 1 |
| 6 | 5 | 2025-04-12 | standard | UPS | 1 |
| 10 | 8 |
Showing first 5 of 27 rows. Latest delivered shipment per purchase.
Showing first 5 of 27 rows. Latest delivered shipment per purchase.
| W3-Atlanta |
| W3-Atlanta |
| UPS |
| UPS |
| economy |
| economy |
| TRK100002 |
| TRK100002 |
| 2025-02-25 |
| 2025-02-25 |
| 2025-03-04 |
| 2025-03-04 |
| 2025-03-05 |
| 2025-03-05 |
| delivered |
| delivered |
| 3.04 |
| 3.04 |
| 5.99 |
| 5.99 |
| 2 | 3 | W4-Dallas | USPS | expedited | TRK100003 | 2025-02-15 | 2025-02-18 | in_transit | 9.2 | 14.99 |
| 2 | 3 | W4-Dallas | USPS | expedited | TRK100003 | 2025-02-15 | 2025-02-18 | in_transit | 9.2 | 14.99 |
| 3 | 4 | W2-Chicago | USPS | standard | TRK100004 | 2025-02-07 | 2025-02-12 | picked_up | 7.17 | 5.0 |
| 3 | 4 | W2-Chicago | USPS | standard | TRK100004 | 2025-02-07 | 2025-02-12 | picked_up | 7.17 | 5.0 |
| 4 | 5 | W3-Atlanta | UPS | standard | TRK100005 | 2025-06-26 | 2025-07-01 | return_to_sender | 6.35 | 5.0 |
| 4 | 5 | W3-Atlanta | UPS | standard | TRK100005 | 2025-06-26 | 2025-07-01 | return_to_sender | 6.35 | 5.0 |
| 5 | 6 | W3-Atlanta | UPS | standard | TRK100006 | 2025-04-08 | 2025-04-13 | 2025-04-12 | delivered | 4.66 | 9.99 |
| 5 | 6 | W3-Atlanta | UPS | standard | TRK100006 | 2025-04-08 | 2025-04-13 | 2025-04-12 | delivered | 4.66 | 9.99 |
| 2024-10-09 |
| 2024-10-09 |
| economy |
| economy |
| UPS |
| UPS |
| 1 |
| 1 |
| 11 | 9 | 2025-06-28 | overnight | USPS | 1 |
| 11 | 9 | 2025-06-28 | overnight | USPS | 1 |
| 13 | 10 | 2025-03-01 | standard | FedEx | 1 |
| 13 | 10 | 2025-03-01 | standard | FedEx | 1 |