Your question is SQL: Top Bottleneck Stage. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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 |