Coding questions · Amazon
Calculate Defect Rate and Identify Top Lanes for Carriers
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.
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.
Shipment Defect Rates and Top Damaged Lanes
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:
- 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.
- 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.
- 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.
Table shipment
| column | type | constraints |
|---|---|---|
| shipment_id | INTEGER | PRIMARY KEY, NOT NULL |
| order_id | INTEGER | NOT NULL |
| ship_date | DATE | NOT NULL |
| carrier | VARCHAR(20) | NOT NULL |
| origin | VARCHAR(3) | NOT NULL |
| destination | VARCHAR(3) | NOT NULL |
| status | VARCHAR(20) | NOT NULL |
Sample data:
| carrier | destination | order_id | origin | ship_date | shipment_id | status |
|---|---|---|---|---|---|---|
| UPS | NYC | 5001 | SEA | 2025-05-05 | 1001 | Delivered |
| FedEx | CHI | 5002 | LAX | 2025-05-06 | 1002 | In-Transit |
| USPS | ATL | 5003 | DAL | 2025-05-07 | 1003 | Delivered |
Table defect
| column | type | constraints |
|---|---|---|
| defect_id | INTEGER | PRIMARY KEY, NOT NULL |
| shipment_id | INTEGER | NOT NULL, FOREIGN KEY REFERENCES shipment(shipment_id) |
| defect_type | VARCHAR(50) | NOT NULL |
| defect_reported_date | DATE | NOT NULL |
Sample data:
| defect_id | defect_reported_date | defect_type | shipment_id |
|---|---|---|---|
| 1 | 2025-05-10 | Damaged | 1001 |
| 2 | 2025-05-11 | Lost | 1002 |
| 3 | 2025-05-12 | LateDelivery | 1002 |
Expected output
One combined result set for carrier defect rate, weekly defect rate with week-over-week change, and top damaged lanes.
| carrier | damaged_defect_count | defect_rate | defects | destination | origin | result_set | shipments | week_start | wow_change_pct |
|---|---|---|---|---|---|---|---|---|---|
| FedEx | NULL | 2 | 2 | NULL | NULL | carrier_defect_rate_30d | 1 | NULL | NULL |
| UPS | NULL | 1 | 1 | NULL | NULL | carrier_defect_rate_30d | 1 | NULL | NULL |
| USPS | NULL | 0 | 0 | NULL | NULL | carrier_defect_rate_30d | 1 | NULL | NULL |
| FedEx | NULL | 2 | 2 | NULL | NULL | carrier_defect_rate_wow_30d | 1 | 2025-05-05 | NULL |
| UPS | NULL | 1 | 1 | NULL | NULL | carrier_defect_rate_wow_30d | 1 | 2025-05-05 | NULL |
| USPS | NULL | 0 | 0 | NULL | NULL | carrier_defect_rate_wow_30d | 1 | 2025-05-05 | NULL |
| NULL | 1 | NULL | NULL | NYC | SEA | top_damaged_lanes_past_quarter | NULL | NULL | NULL |
Hints
- Use COUNT(DISTINCT shipment_id) for shipment counts when joining to defects.
- DATE_TRUNC('week', ship_date) gives Monday-start weeks in PostgreSQL.
- Use LAG over carrier and week_start for week-over-week change.