Your question is Compare Vendor and Queue Performance. 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.
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.
| Column | Type | Description |
|---|---|---|
| ticket_idPK | INT | Primary key for each ticket |
| vendor_id | INT | References the vendor that handled the ticket |
| queue_id | INT | References the queue the ticket was routed to |
| 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 |
| Column | Type | Description |
|---|---|---|
| vendor_idPK | INT | Primary key for each vendor |
| vendor_name | VARCHAR(100) | Vendor name |
| Column | Type | Description |
|---|---|---|
| queue_idPK | INT | Primary key for each queue |
| queue_name | VARCHAR(100) | Queue name |