Top Failure Steps in 30 Days | Dataford Interview Questions

Write a SQL query to identify the top three manufacturing steps with the highest failure rates over the last thirty days.

Use completed_at to define the thirty-day period. Calculate each step's failure rate as failed runs divided by all runs for that step.

Output

Return one row per manufacturing step.
Include step_name, total_runs, failed_runs, and failure_rate_pct.
Sort by failure rate descending, then total runs descending, then step name ascending. Return only the top three steps.

manufacturing_steps

Column	Type	Description
step_idPK	INT	Unique identifier for a manufacturing step
step_name	VARCHAR(100)	Name of the manufacturing step

process_runs

Column	Type	Description
run_idPK	INT	Unique identifier for a process run
step_id	INT	Manufacturing step associated with the run
completed_at	TIMESTAMP	Timestamp when the run completed
outcome	VARCHAR(20)	Run outcome, such as PASSED or FAILED

Your question is Top Failure Steps in 30 Days. 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.