Identify Key Metrics for Monitoring Shipment Defects

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.

Top carriers by shipment defect rate over the last 30 days

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.

Table shipment

column	type	constraints
shipment_id	INTEGER	PRIMARY KEY, NOT NULL
order_id	INTEGER	NOT NULL
ship_date	DATE	NOT NULL
carrier	VARCHAR(50)	NOT NULL
status	VARCHAR(20)	NOT NULL

Sample data:

carrier	order_id	ship_date	shipment_id	status
UPS	5001	2025-05-10	1001	shipped
FedEx	5002	2025-05-15	1002	shipped
DHL	5003	2025-05-20	1003	delayed

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
detected_date	DATE	NOT NULL

Sample data:

defect_id	defect_type	detected_date	shipment_id
9001	broken_item	2025-05-18	1001
9002	missing_part	2025-05-22	1003
9003	late_ship	2025-05-25	1003

Expected output

For shipments with ship_date between '2025-05-03' and '2025-06-01' (inclusive), return one row per carrier with columns: carrier, total_shipments, total_defective_shipments (shipments that have at least one associated defect), and defect_rate = total_defective_shipments / total_shipments. Order by defect_rate descending, then total_shipments descending, then carrier ascending, and return the top 3 carriers.

carrier	defect_rate	total_defective_shipments	total_shipments
DHL	1	1	1
UPS	1	1	1
FedEx	0	0	1

Hints

1. Filter shipments to the last 30 days explicitly, e.g., ship_date BETWEEN '2025-05-03' AND '2025-06-01'.
2. LEFT JOIN shipment to defect so that non-defective shipments are still counted in total_shipments.
3. Use COUNT(DISTINCT shipment_id) on the defect side (or a pre-aggregated CTE) to avoid double-counting shipments with multiple defects.
4. Group by carrier and compute defect_rate as total_defective_shipments * 1.0 / total_shipments or via AVG over a CASE expression.
5. Order by defect_rate DESC, then total_shipments DESC, and limit to the top 3 carriers.