SQL for Production Performance | Dataford Interview Questions

Write a SQL query to analyze production performance by line, shift, and day, including output, waste, and downtime metrics at Stouffers. Use completed production runs and the provided line and shift reference data.

Output

One row per line, shift, and production day with valid reference matches.
Columns: line_name, shift_name, production_day, output_units, waste_units, downtime_minutes, and waste_percentage.
Include totals for each combination, calculate waste as a percentage of output plus waste, and sort by line, shift, and day.

Schema

production_runs

Column	Type	Description
run_idPK	INT	Unique production run identifier
line_id	INT	Production line identifier
shift_id	INT	Shift identifier
production_timestamp	TIMESTAMP	Timestamp when the run was recorded
output_units	INT	Units produced during the run
waste_units	INT	Units wasted during the run
downtime_minutes	INT	Minutes of downtime during the run
status	VARCHAR(20)	Run status

production_lines

Column	Type	Description
line_idPK	INT	Unique production line identifier
line_name	VARCHAR(50)	Production line name
facility	VARCHAR(80)	Facility containing the line

shifts

Column	Type	Description
shift_idPK	INT	Unique shift identifier
shift_name	VARCHAR(30)	Shift name
shift_start	TIME	Scheduled shift start time

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