Identify Key Metrics for Monitoring Shipment Defects
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
shipment
+-------------+----------+------------+---------+---------+
| shipment_id | order_id | ship_date | carrier | status |
+-------------+----------+------------+---------+---------+
| 1001 | 5001 | 2023-07-01 | UPS | shipped |
| 1002 | 5002 | 2023-07-02 | FedEx | shipped |
| 1003 | 5003 | 2023-07-03 | DHL | delayed |
+-------------+----------+------------+---------+---------+
defect
+-----------+-------------+--------------+--------------+
| defect_id | shipment_id | defect_type | detected_date|
+-----------+-------------+--------------+--------------+
| 9001 | 1001 | broken_item | 2023-07-05 |
| 9002 | 1003 | missing_part | 2023-07-06 |
| 9003 | 1003 | late_ship | 2023-07-07 |
+-----------+-------------+--------------+--------------+
##### Scenario
Amazon logistics team wants to monitor product quality by tracking defects related to customer shipments.
##### Question
Which metrics would you define to monitor shipment defects on an ongoing basis? Write the SQL that calculates, for the last 30 days, each carrier’s total shipments, total defective shipments, and defect_rate (= defective/total). Return the top-3 carriers with the highest defect_rate.
##### Hints
Join shipment and defect, filter on ship_date or detected_date, GROUP BY carrier, use COUNT(DISTINCT) or COUNT(*) as appropriate; compute defect_rate in a CTE or HAVING clause.
Overview: This question evaluates a data scientist's skill in defining and computing operational monitoring metrics for shipment defects, emphasizing data manipulation, aggregation, and metric definition using SQL and Python.
You are given two tables, shipment and defect. The logistics team wants to monitor product quality by tracking defects related to customer shipments.
Define metrics to monitor shipment defects and write a SQL query that, for shipments with ship_date between '2025-05-03' and '2025-06-01' (inclusive), returns for each carrier:
- total_shipments
- total_defective_shipments (shipments that have at least one associated defect)
- defect_rate = total_defective_shipments / total_shipments
Return the top 3 carriers with the highest defect_rate, ordering by defect_rate descending, then by total_shipments descending, then by carrier ascending to break ties.
Tables
shipment(shipment_id INTEGER, order_id INTEGER, ship_date DATE, carrier VARCHAR(50), status VARCHAR(20))
defect(defect_id INTEGER, shipment_id INTEGER, defect_type VARCHAR(50), detected_date DATE)
Hints
- Filter shipments to the last 30 days explicitly, e.g., ship_date BETWEEN '2025-05-03' AND '2025-06-01'.
- LEFT JOIN shipment to defect so that non-defective shipments are still counted in total_shipments.