Amazon: Find which fulfillment center is responsible for the region's missed delivery promises.
The Midwest operations lead has had complaints from customer service about packages arriving after the promised date, and wants to know where the problem actually sits before raising it with the center managers. They have shared two exports from the delivery tracking system: one of shipments from the last two weeks, and one describing the fulfillment centers in the region.
shipments.csv has one row per shipment record from the tracking system:
shipment_idcenter_id - the fulfillment center that dispatched the shipmentpromised_at - the delivery time promised to the customerdelivered_at - the delivery time reported by the carrier's tracking systemorder_value_usdcarrierfulfillment_centers.csv has one row per fulfillment center:
center_idcenter_nameregionopened_on - the date the center began operatingYou can solve this in Python, SQL, or a mix of both — StrataScratch notebooks support both languages in the same notebook.
Assume this data comes directly from the delivery tracking system and hasn't been cleaned, so review it carefully before relying on the numbers. When something looks off, work out what it is and decide how to handle it; when a definition or a final decision is genuinely ambiguous, choose a reasonable option and explain why. There's often more than one valid way to compute a result, so explain the approach you take and the reasoning behind your choices.
What's assessed: your results, and how clearly you explain what you see in the data and justify the choices you make.
See the full interview round by round, with questions for each stage.