Split Shipment Rate

Amazon: Find which product category most often ships an order in more than one package.

Assignment

Fulfillment is reviewing the cost of sending a single order out as several separate packages, and wants to know where it happens most before deciding what to consolidate. They have shared two exports from the fulfillment system: one of packages sent over the last three weeks, and one naming the product categories.

Deliverables

• The average value of an order.
• The share of orders in each category that were sent as more than one package.
• Which category to prioritize for consolidation, and the reasoning behind that choice.
• A short closing report of your findings for the fulfillment team, including anything they should double-check before relying on the numbers.

Data Description

order_packages.csv has one row per package created by the fulfillment system:

• package_id
• order_id
• category_id
• order_total_usd
• shipped_on
• package_status
• is_replacement - whether the package was sent to replace an earlier one

product_categories.csv has one row per product category:

• category_id
• category_name

Practicalities

You 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 fulfillment 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.