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 |
| Column | Type | Description |
|---|---|---|
| ticket_idPK | INT | Unique support ticket identifier |
| issue_type | VARCHAR(50) | Category of the support issue |
| status | VARCHAR(20) | Current ticket status |
| created_date | DATE | Date the ticket was created |
| resolution_hours | DECIMAL(6,2) | Hours required to resolve the ticket |
{"lyft_support_tickets":[["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"],["106","Payment","resolved","2024-02-01","3.00"],["107","Account","resolved","2024-01-22","6.00"],["108","Navigation","closed","2024-01-09","5.00"],["109","Safety","resolved","2024-01-28","8.00"],["110","Payment","resolved","2024-01-03",Output[["Payment","3","3.50"],["Account","2","7.00"],["Safety","2","10.00"],["null","1","7.00"]]