Write a SQL query to identify the top 5 products by monthly defect rate, joining production, quality, and shipment tables.
Calculate defect rate as total defects divided by shipped units, using shipped records only. Return the five highest-rate products for each calendar month.
month, product_id, total_defects, shipped_units, defect_rate, and monthly_rank.product_id as the tie-breaker.| Column | Type | Description |
|---|---|---|
| production_idPK | INT | Unique production lot identifier |
| product_id | VARCHAR(20) | NXP Semiconductors product identifier |
| production_date | DATE | Date the production lot was completed |
| units_produced | INT | Units produced in the lot |
| Column | Type | Description |
|---|---|---|
| quality_idPK | INT | Unique quality inspection identifier |
| production_id | INT | Production lot inspected |
| defect_count | INT | Defective units recorded during inspection |
| Column | Type | Description |
|---|---|---|
| shipment_idPK | INT | Unique shipment identifier |
| production_id | INT | Production lot associated with the shipment |
| shipped_units | INT | Units included in the shipment record |
| shipment_status | VARCHAR(20) | Shipment lifecycle status |