Rank Facilities by Cycle Time | Dataford Interview Questions

You are given manufacturing data for assembly runs across facilities and product lines. Write a PostgreSQL query that returns each facility’s average assembly cycle time for every product line, then ranks facilities within each product line from fastest to slowest using a window function. Only include completed runs, and use the product line name in the final output.

Schema

assembly_runs

Column	Type	Description
run_idPK	INT	Unique assembly run identifier
facility_id	INT	Facility where the assembly run occurred
product_line_id	INT	Product line associated with the run
cycle_time_minutes	NUMERIC(8,2)	Assembly cycle time in minutes
run_status	VARCHAR(20)	Status of the run

facilities

Column	Type	Description
facility_idPK	INT	Unique facility identifier
facility_name	VARCHAR(100)	Facility name

product_lines

Column	Type	Description
product_line_idPK	INT	Unique product line identifier
product_line_name	VARCHAR(100)	Product line name

Your question is Rank Facilities by Cycle Time. 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