NovaOps changed its support workflow on 2024-02-01 and wants a simple before-vs-after comparison. Write a SQL query to compare average ticket resolution time and ticket volume for the two periods.
support_tickets table only.Before if resolved_date is before 2024-02-01, otherwise After.resolved_date or resolution_hours is NULL.period_labelticket_countavg_resolution_hoursperiod_label.| column | type | description |
|---|---|---|
| ticket_id | INT | Primary key for each support ticket |
| team_name | VARCHAR(50) | Support team that handled the ticket |
| created_date | DATE | Date the ticket was created |
| resolved_date | DATE | Date the ticket was resolved |
| resolution_hours | DECIMAL(6,2) | Total hours to resolve the ticket |
| priority | VARCHAR(20) | Ticket priority |
| ticket_id | team_name | created_date | resolved_date | resolution_hours | priority |
|---|---|---|---|---|---|
| 1 | Alpha | 2024-01-05 | 2024-01-06 | 10.50 | High |
| 2 | Beta | 2024-02-03 | 2024-02-03 | 4.00 | Low |
| 3 | Alpha | 2024-01-20 | 2024-01-22 | 18.00 | Medium |
| 4 | Gamma | 2024-02-10 | 2024-02-12 | 30.00 | High |
| 5 | Beta | 2024-01-28 | 2024-01-31 | 12.00 | Medium |
| 6 | Alpha | 2024-02-01 | 2024-02-02 | 8.00 | High |
| 7 | Gamma | 2024-01-15 | NULL | NULL | Low |
| 8 | Beta | 2024-02-14 | 2024-02-14 | 0.00 | Low |
| 9 | Alpha | 2024-01-31 | 2024-02-01 | 6.00 | Medium |
| 10 | Gamma | 2024-02-20 | 2024-02-25 | 50.00 | Critical |
| period_label | ticket_count | avg_resolution_hours |
|---|---|---|
| After | 6 | 16.33 |
| Before | 4 | 13.13 |
| Column | Type | Description |
|---|---|---|
| ticket_idPK | INT | Primary key for each support ticket |
| team_name | VARCHAR(50) | Support team assigned to the ticket |
| created_date | DATE | Date the ticket was created |
| resolved_date | DATE | Date the ticket was resolved |
| resolution_hours | DECIMAL(6,2) | Hours taken to resolve the ticket |
| priority | VARCHAR(20) | Ticket priority level |
{"support_tickets":[["1","Alpha","2024-01-05","2024-01-06","10.50","High"],["2","Beta","2024-02-03","2024-02-03","4.00","Low"],["3","Alpha","2024-01-20","2024-01-22","18.00","Medium"],["4","Gamma","2024-02-10","2024-02-12","30.00","High"],["5","Beta","2024-01-28","2024-01-31","12.00","Medium"],["6","Alpha","2024-02-01","2024-02-02","8.00","High"],["7","Gamma","2024-01-15",null,null,"Low"],["8","Beta","2024-02-14","2024-02-14","0.00","Low"],["9","Alpha","2024-01-31","2024-02-01","6.00","Medium"],Output[["After","13","28.85"],["Before","8","20.31"]]