Task
Nimbus Logistics tracks support tickets in Excel today and wants the same operational performance view in SQL. Write a PostgreSQL query to summarize monthly ticket activity by team.
Requirements
- Return one row per
team_name and month for closed tickets only.
- Show the month as
YYYY-MM using the ticket closed_date.
- Calculate the number of closed tickets and the average resolution hours.
- Sort the results by month ascending, then
closed_ticket_count descending.
Table Definition
support_tickets
| Column | Type | Description |
|---|
| ticket_id | INT | Unique ticket identifier |
| team_name | VARCHAR(50) | Operations team handling the ticket |
| status | VARCHAR(20) | Current ticket status |
| created_date | DATE | Date the ticket was created |
| closed_date | DATE | Date the ticket was closed |
| resolution_hours | DECIMAL(6,2) | Hours taken to resolve the ticket |
| priority | VARCHAR(20) | Ticket priority |
Sample Data
| ticket_id | team_name | status | created_date | closed_date | resolution_hours | priority |
|---|
| 101 | Fulfillment | Closed | 2024-02-03 | 2024-02-04 | 5.50 | High |
| 102 | Returns | Open | 2024-02-05 | | | Medium |
| 103 | Fulfillment | Closed | 2024-01-28 | 2024-02-01 | 18.00 | Low |
| 104 | Dispatch | Closed | 2024-02-10 | 2024-02-10 | 2.00 | High |
| 105 | Returns | Closed | 2024-01-31 | 2024-02-02 | 7.25 | Medium |
| 106 | Dispatch | Pending | 2024-02-11 | | | Low |
Expected Output
| close_month | team_name | closed_ticket_count | avg_resolution_hours |
|---|
| 2024-01 | Dispatch | 1 | 0.00 |
| 2024-01 | Fulfillment | 1 | 12.00 |
| 2024-02 | Fulfillment | 2 | 11.75 |
| 2024-02 | Returns | 2 | 8.63 |
| 2024-02 | Dispatch | 1 | 2.00 |
