Task
You are given ticket-level operational data and need to compare performance across vendors and queues. Write a PostgreSQL query that returns each vendor and queue pair with the number of tickets handled, average handle time in minutes, and resolution rate for tickets created in the last 30 days. Exclude tickets that are still open, and sort the result by highest resolution rate, then lowest average handle time.
Schema
tickets
| column | type | description |
|---|
| ticket_id | INT | Primary key for each ticket |
| vendor_id | INT | Foreign key to vendors.vendor_id |
| queue_id | INT | Foreign key to queues.queue_id |
| created_at | TIMESTAMP | Ticket creation timestamp |
| closed_at | TIMESTAMP | Ticket close timestamp, nullable if still open |
| status | VARCHAR(20) | Ticket status such as open, closed, or pending |
| handle_time_minutes | INT | Time spent handling the ticket |
vendors
| column | type | description |
|---|
| vendor_id | INT | Primary key for each vendor |
| vendor_name | VARCHAR(100) | Vendor name |
queues
| column | type | description |
|---|
| queue_id | INT | Primary key for each queue |
| queue_name | VARCHAR(100) | Queue name |
Sample data
| tickets.ticket_id | vendor_id | queue_id | created_at | closed_at | status | handle_time_minutes |
|---|
| 1 | 1 | 10 | 2024-05-20 09:00:00 | 2024-05-20 09:18:00 | closed | 18 |
| 2 | 1 | 10 | 2024-05-21 10:00:00 | 2024-05-21 10:25:00 | closed | 25 |
| 3 | 1 | 11 | 2024-05-22 11:00:00 | null | open | 12 |
| 4 | 2 | 10 | 2024-05-23 12:00:00 | 2024-05-23 12:40:00 | closed | 40 |
| 5 | 2 | 11 | 2024-05-24 13:00:00 | 2024-05-24 13:10:00 | closed | 10 |
| 6 | 2 | 11 | 2024-04-10 14:00:00 | 2024-04-10 14:30:00 | closed | 30 |
| 7 | 3 | 10 | 2024-05-25 15:00:00 | 2024-05-25 15:05:00 | closed | 5 |
| 8 | 3 | 12 | 2024-05-26 16:00:00 | null | pending | 8 |
| 9 | 3 | 12 | 2024-05-27 17:00:00 | 2024-05-27 17:22:00 | closed | 22 |
| 10 | 4 | 11 | 2024-05-28 18:00:00 | 2024-05-28 18:55:00 | closed | 55 |
| vendors.vendor_id | vendor_name |
|---|
| 1 | Alpha Support |
| 2 | Beacon Ops |
| 3 | Cedar Services |
| 4 | Delta Assist |
| queues.queue_id | queue_name |
|---|
| 10 | Billing |
| 11 | Technical |
| 12 | Account Access |
| 13 | Escalations |
Expected output
| vendor_name | queue_name | ticket_count | avg_handle_time_minutes | resolution_rate |
|---|
| Alpha Support | Billing | 2 | 21.5 | 1.00 |
| Beacon Ops | Billing | 1 | 40.0 | 1.00 |
| Beacon Ops | Technical | 1 | 10.0 | 1.00 |
| Cedar Services | Billing | 1 | 5.0 | 1.00 |
| Cedar Services | Account Access | 1 | 22.0 | 1.00 |
