Rank Steps with Window Functions | Dataford Interview Questions

Write a SQL query using window functions to rank manufacturing steps by failure rate within each product line.

Use the provided manufacturing step and run records. Include defined steps with no runs, treating their failure rate as 0%.

Output

One row per manufacturing step, with product_line, step_name, failure_rate, and step_rank.
Rank steps within each product line by failure rate descending, with equal rates sharing a rank.
Order by product_line, failure_rate descending, and step_name ascending.

manufacturing_steps

Column	Type	Description
step_idPK	INT	Unique manufacturing step identifier
product_line	VARCHAR(50)	Product line containing the step
step_name	VARCHAR(100)	Manufacturing step name

manufacturing_runs

Column	Type	Description
run_idPK	INT	Unique manufacturing run identifier
step_id	INT	Manufacturing step evaluated by the run
run_date	DATE	Date of the manufacturing run
status	VARCHAR(20)	Run outcome, such as PASS or FAIL

Your question is Rank Steps with Window Functions. 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.