Task
Lyft's operations team often recreates Excel-style pivot tables to monitor support workload. Write a SQL query to summarize the number of driver support tickets by queue, with separate counts for each ticket status.
Requirements
- Return one row per
queue_name.
- Count how many tickets in each queue are
open, resolved, and escalated using conditional aggregation.
- Exclude rows where
queue_name is NULL.
- Order the result by
open_tickets descending, then queue_name ascending.
Table Definition
driver_support_tickets
| column | type | description |
|---|
| ticket_id | INT | Unique ticket identifier |
| queue_name | VARCHAR(100) | Lyft support queue handling the ticket |
| ticket_status | VARCHAR(20) | Current status of the ticket |
| city | VARCHAR(100) | City tied to the issue |
| created_date | DATE | Date the ticket was created |
Sample Data
| ticket_id | queue_name | ticket_status | city | created_date |
|---|
| 101 | Driver Onboarding | open | Chicago | 2024-06-03 |
| 102 | Safety Escalations | escalated | San Francisco | 2024-06-01 |
| 103 | Driver Onboarding | resolved | Atlanta | 2024-06-02 |
| 104 | Earnings Review | open | Seattle | 2024-06-04 |
| 105 | Earnings Review | resolved | Chicago | 2024-06-01 |
| 106 | Driver Onboarding | open | Miami | 2024-06-05 |
Expected Output
| queue_name | open_tickets | resolved_tickets | escalated_tickets |
|---|
| Driver Onboarding | 3 | 1 | 0 |
| Earnings Review | 2 | 1 | 1 |
| Safety Escalations | 1 | 1 | 2 |
| Background Check | 0 | 1 | 0 |
