SQL Production Performance Analysis | Dataford Interview Questions

Write a SQL query to analyze production performance by plant, line, and week, including output, defect rate, and on-time delivery rate.

Use the provided production, line, and delivery data. Include production weeks with no delivery records, and treat missing defect or delivery-status values appropriately.

Output

One row per plant, line, and calendar week
Columns: plant_name, line_name, week_start, total_output, defect_rate_pct, and on_time_delivery_rate_pct
Include all production groups, order by plant, line, and week, and round percentage values to two decimal places

Schema

production_runs

Column	Type	Description
run_idPK	INT	Production run identifier
line_id	INT	Production line identifier
production_date	DATE	Date of the production run
units_output	INT	Units produced during the run
defective_units	INT	Units identified as defective

lines

Column	Type	Description
line_idPK	INT	Production line identifier
plant_name	VARCHAR(100)	Plant name
line_name	VARCHAR(100)	Production line name

deliveries

Column	Type	Description
delivery_idPK	INT	Delivery identifier
run_id	INT	Related production run identifier
on_time	BOOLEAN	Whether the delivery met its scheduled date

Your question is SQL Production Performance Analysis. 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