SQL Query for Cycle-Time Bottlenecks

MediumSQL · PostgreSQL

Problem

Write a business-flavored SQL query to identify production bottlenecks by analyzing cycle times across different manufacturing stages.

Use the provided production_runs, stage_cycles, and manufacturing_stages tables. Include only completed runs and completed cycles with non-null start and end timestamps.

Output

One row per manufacturing stage with at least one qualifying cycle
Columns: stage_id, stage_name, completed_cycle_count, avg_cycle_hours, max_cycle_hours, and bottleneck_rank
Rank stages by average cycle time in descending order, with tied stages sharing a rank
Order by bottleneck_rank, then stage_id

Schema

production_runs

Column	Type	Description
run_idPK	INT	Unique production run identifier
product_code	VARCHAR(30)	Product or system code manufactured in the run
started_at	TIMESTAMP	Production run start timestamp
completed_at	TIMESTAMP	Production run completion timestamp
status	VARCHAR(20)	Production run status

manufacturing_stages

Column	Type	Description
stage_idPK	INT	Unique manufacturing stage identifier
stage_name	VARCHAR(80)	Manufacturing stage name
sequence_no	INT	Sequence of the stage in the manufacturing flow
area	VARCHAR(60)	Manufacturing area containing the stage

stage_cycles

Column	Type	Description
cycle_idPK	INT	Unique stage cycle identifier
run_id	INT	Production run containing the stage cycle
stage_id	INT	Manufacturing stage associated with the cycle
started_at	TIMESTAMP	Stage cycle start timestamp
ended_at	TIMESTAMP	Stage cycle end timestamp
status	VARCHAR(20)	Stage cycle status

Tablesproduction_runsstage_cyclesmanufacturing_stages