Write a SQL query to identify the top 5 operational bottlenecks by region using orders, SLA breaches, and support tickets. Treat each SLA breach and support ticket as one issue, and calculate a bottleneck score as their combined count. Include regions with orders even when they have no breaches or tickets.
region, order_count, sla_breach_count, support_ticket_count, bottleneck_scorebottleneck_score descending, then region ascending to break ties| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Unique order identifier |
| region | VARCHAR(50) | Operational region handling the order |
| order_date | DATE | Date the order was placed |
| customer_reference | VARCHAR(50) | Customer reference associated with the order |
| Column | Type | Description |
|---|---|---|
| breach_idPK | INT | Unique SLA breach identifier |
| order_id | INT | Order associated with the breach |
| breach_date | DATE | Date the SLA breach was recorded |
| severity | VARCHAR(20) | Severity classification of the breach |
| Column | Type | Description |
|---|---|---|
| ticket_idPK | INT | Unique support ticket identifier |
| order_id | INT | Order associated with the ticket |
| created_date | DATE | Date the ticket was created |
| status | VARCHAR(20) | Current ticket status |