Calculate Defect Rate and Identify Top Lanes for Carriers
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
shipment
+-------------+----------+-----------+---------+---------+-------------+-----------+
| shipment_id | order_id | ship_date | carrier | origin | destination | status |
+-------------+----------+-----------+---------+---------+-------------+-----------+
| 1001 | 5001 | 2023-09-01| UPS | SEA | NYC | Delivered |
| 1002 | 5002 | 2023-09-02| FedEx | LAX | CHI | In-Transit|
| 1003 | 5003 | 2023-09-03| USPS | DAL | ATL | Delivered |
+-------------+----------+-----------+---------+---------+-------------+-----------+
defect
+-----------+-------------+-------------+----------------------+
| defect_id | shipment_id | defect_type | defect_reported_date |
+-----------+-------------+-------------+----------------------+
| 1 | 1001 | Damaged | 2023-09-05 |
| 2 | 1002 | Lost | 2023-09-06 |
| 3 | 1002 | LateDelivery| 2023-09-07 |
+-----------+-------------+-------------+----------------------+
##### Scenario
Amazon Logistics team wants to monitor and reduce shipment defects across carriers and shipment lanes.
##### Question
Given tables shipment and defect, write SQL to calculate the defect rate (number of defects ÷ number of shipments) for each carrier in the last 30 days. Extend your query to show the week-over-week percentage change in defect rate for each carrier using window functions. Identify the top 3 origin-destination lanes with the highest count of 'Damaged' defects in the past quarter.
##### Hints
Join shipment and defect, filter by date, aggregate counts, compute rates, use LAG for WoW change.
Overview: This question evaluates competency in SQL-based data manipulation for a data scientist role, covering joins, aggregations, date-based filtering, metric computation, and use of window functions to derive week-over-week changes.
Using the shipment and defect tables, assume the analysis date is 2025-06-01. Return one combined result set with columns result_set, carrier, shipments, defects, defect_rate, week_start, wow_change_pct, origin, destination, and damaged_defect_count:
1. result_set = 'carrier_defect_rate_30d': for shipments from 2025-05-02 through 2025-06-01 inclusive, calculate defects divided by shipments for each carrier.
2. result_set = 'carrier_defect_rate_wow_30d': for the same period, compute weekly defect rate by carrier using Monday-start DATE_TRUNC('week', ship_date), plus week-over-week percentage change in defect rate using LAG.
3. result_set = 'top_damaged_lanes_past_quarter': for 2025-03-01 through 2025-06-01 inclusive, return the top 3 origin-destination lanes by count of 'Damaged' defects.
Fields that do not apply to a row should be NULL.
Tables
shipment(shipment_id INTEGER, order_id INTEGER, ship_date DATE, carrier VARCHAR(20), origin VARCHAR(3), destination VARCHAR(3), status VARCHAR(20))
defect(defect_id INTEGER, shipment_id INTEGER, defect_type VARCHAR(50), defect_reported_date DATE)
Hints
- Use COUNT(DISTINCT shipment_id) for shipment counts when joining to defects.
- DATE_TRUNC('week', ship_date) gives Monday-start weeks in PostgreSQL.