Task
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.
Requirements
- Use the
support_tickets table only.
- Label each ticket as
Before if resolved_date is before 2024-02-01, otherwise After.
- Exclude tickets where
resolved_date or resolution_hours is NULL.
- Return one row per period with:
period_labelticket_countavg_resolution_hours
- Order results by
period_label.
Table Definition
| 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 |
| | |
Sample Data
| 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 |
Expected Output
| period_label | ticket_count | avg_resolution_hours |
|---|
| After | 6 | 16.33 |
| Before | 4 | 13.13 |
