Write a SQL query to identify the top bottleneck stage in a multi-step operations workflow using window functions.
Use the supplied workflow run, stage, and stage event data. Consider only completed workflow runs and completed stage events. Calculate average stage duration in minutes for each workflow type, select the highest-duration stage, and resolve ties by the lowest stage order, then stage ID.
workflow_type, stage_name, average_duration_minutes, completed_observations, and bottleneck_rank.workflow_type.| Column | Type | Description |
|---|---|---|
| run_idPK | INT | Unique workflow run identifier |
| workflow_type | VARCHAR(50) | Workflow category executed by the run |
| status | VARCHAR(20) | Overall workflow status |
| Column | Type | Description |
|---|---|---|
| stage_idPK | INT | Unique configured stage identifier |
| workflow_type | VARCHAR(50) | Workflow category containing the stage |
| stage_name | VARCHAR(100) | Human-readable stage name |
| stage_order | INT | Position of the stage in its workflow |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique stage event identifier |
| run_id | INT | Workflow run associated with the event |
| stage_id | INT | Configured stage associated with the event |
| status | VARCHAR(20) | Stage event status |
| started_at | TIMESTAMP | Timestamp when stage processing began |
| completed_at | TIMESTAMP | Timestamp when stage processing finished |