00:00

2008. Latest Delivered Shipment per Purchase

MediumSQLITE71 / 1584

Relevant companies

Amazon

Walmart

Target

Instacart

Shopify

eBay

Etsy

Wayfair

Chewy

Kroger

Costco

Best Buy

Home Depot

IKEA

Alibaba

Flipkart

Mercado Libre

Rakuten

Faire

Coupang

2008. Latest Delivered Shipment per Purchase

Medium71 / 1584

Relevant companies

Amazon

Walmart

Target

Instacart

Shopify

eBay

Etsy

Wayfair

Chewy

Kroger

Costco

Best Buy

Home Depot

IKEA

Alibaba

Flipkart

Mercado Libre

Rakuten

Faire

Coupang

Sign in to Practice

Write SQL and Python, run instantly in your browser, and track your progress.

Runs live in your browser — no setup
Save progress across all 1,500+ problems
Submit and validate your answer instantly

Business Problem

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.

Schema: shipments

Column Name	Type
shipment_id	INTEGER
order_id	INTEGER
warehouse_code	TEXT

Business Problem

Schema: shipments

Column Name	Type
shipment_id	INTEGER
order_id	INTEGER
warehouse_code	TEXT

Latest Delivered Shipment per Purchase — SQL Medium | Let's Data Science | Let's Data Science

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

2008. Latest Delivered Shipment per Purchase

Relevant companies

Tags

2008. Latest Delivered Shipment per Purchase

Relevant companies

Tags

Sign in to Practice