SQL for Plant Performance | Dataford Interview Questions

Write a SQL query to track weekly production output, defect rate, and on-time shipment by plant, and compare each plant against the Delta Electronics Americas company average. Use all available production records and report weeks beginning on Monday. Treat missing on-time shipment counts as unavailable when calculating the rate.

Output

One row per plant and production week, ordered by week and plant name.
Include week_start, plant_name, output_units, defect_rate_pct, on_time_rate_pct, the three corresponding company averages, and each plant's variance from those averages.
Round percentage and variance values to two decimal places.

Schema

plants

Column	Type	Description
plant_idPK	INT	Unique plant identifier
plant_name	VARCHAR(100)	Delta Electronics Americas plant name
region	VARCHAR(50)	Geographic region of the plant

production_records

Column	Type	Description
record_idPK	INT	Unique production record identifier
plant_id	INT	Plant associated with the production record
production_date	DATE	Date of production activity
units_produced	INT	Number of units produced
defective_units	INT	Number of defective units
shipments	INT	Number of shipments dispatched
on_time_shipments	INT	Number of shipments delivered on time

Your question is SQL for Plant Performance. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem