Lyft Operations wants a quick summary of issue volume from a raw export similar to what someone might first inspect in Excel. Write a SQL query to turn the raw ticket data into an actionable summary.
Return the number of support tickets and the average resolution time, grouped by issue_type, for only tickets created in January 2024 with status resolved.
created_date is in January 2024.status = 'resolved'.issue_type.issue_type, ticket_count, and avg_resolution_hours.ticket_count descending, then issue_type ascending.lyft_support_tickets
| column | type | description |
|---|---|---|
| ticket_id | INT | Unique ticket identifier |
| issue_type | VARCHAR(50) | Category of rider or driver issue |
| status | VARCHAR(20) | Current ticket status |
| created_date | DATE | Date the ticket was created |
| resolution_hours | DECIMAL(6,2) | Hours taken to resolve the ticket |
| ticket_id | issue_type | status | created_date | resolution_hours |
|---|---|---|---|---|
| 101 | Payment | resolved | 2024-01-12 | 4.50 |
| 102 | Safety | open | 2024-01-15 | NULL |
| 103 | Payment | resolved | 2024-01-20 | 2.00 |
| 104 | Account | resolved | 2024-01-05 | 8.00 |
| 105 | Safety | resolved | 2024-01-18 | 12.00 |
| issue_type | ticket_count | avg_resolution_hours |
|---|---|---|
| Payment | 3 | 3.50 |
| Account | 2 | 7.00 |
| Safety | 2 | 10.00 |