SQL: Production, Defects, On-Time | Dataford Interview Questions

Write a SQL query to calculate weekly production output, defect rate, and on-time delivery by plant from operational data.

Use the plants, production_batches, and delivery_records tables. Treat missing defect counts as zero, include production batches without a delivery record, and calculate each week from the production date.

Output

One row per plant and production week.
Columns: plant_name, week_start, total_units_produced, defect_rate_pct, and on_time_delivery_rate_pct.
Include weeks with production but no delivery, return NULL when a rate has no valid denominator, and sort by plant name and week start.

Schema

plants

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

production_batches

Column	Type	Description
batch_idPK	INT	Unique production batch identifier
plant_id	INT	Plant that produced the batch
production_date	DATE	Date production was completed
units_produced	INT	Total units produced in the batch
defect_units	INT	Units classified as defective

delivery_records

Column	Type	Description
delivery_idPK	INT	Unique delivery record identifier
batch_id	INT	Production batch associated with the delivery
due_date	DATE	Promised delivery date
delivered_date	DATE	Actual delivery date

Your question is SQL: Production, Defects, On-Time. Start with the requirements and the three 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