SQL for Production Bottleneck Analysis | Dataford Interview Questions

Write a SQL query to analyze production efficiency by plant, shift, and week, joining production output and downtime tables to identify the top bottlenecks.

Calculate production totals, planned units, downtime minutes, efficiency percentage, and prior-week downtime. Include unmatched production or downtime groups, and identify the two highest-downtime groups per plant.

Output

One row per selected plant, shift, and week.
Columns: plant_name, shift_name, week_start, total_units_produced, planned_units, total_downtime_minutes, efficiency_pct, prior_week_downtime_minutes, and bottleneck_rank.
Return the top two bottlenecks per plant, ordered by plant, rank, week, and shift.

Schema

production_output

Column	Type	Description
output_idPK	INT	Production output record identifier
plant_name	VARCHAR(100)	Manufacturing plant name
shift_name	VARCHAR(30)	Production shift
production_date	DATE	Date of production
units_produced	INT	Units produced during the record
planned_units	INT	Planned units for the production record

downtime

Column	Type	Description
downtime_idPK	INT	Downtime event identifier
plant_name	VARCHAR(100)	Manufacturing plant name
shift_name	VARCHAR(30)	Shift during the downtime event
downtime_date	DATE	Date of downtime event
downtime_minutes	INT	Downtime duration in minutes
reason	VARCHAR(100)	Downtime reason

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