Write a SQL query to calculate weekly production output, defect rate, and on-time shipment rate by plant and product line.
Use production_runs, shipments, and plants. Define the production week from production_date and the shipment week from promised_ship_date. Defect rate is defective units divided by produced units. On-time shipment rate is units shipped on or before the promised date divided by total units shipped. Include weeks represented by either production or shipments.
Output
- One row per plant, product line, and week.
- Return
plant_name, product_line, week_start, production_output, defect_rate, and on_time_shipment_rate.
- Sort by plant name, product line, and week start ascending.
