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.
team_name and month for closed tickets only.YYYY-MM using the ticket closed_date.closed_ticket_count descending.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 |
| 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 |
| 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 |
| Column | Type | Description |
|---|---|---|
| ticket_idPK | INT | Unique ticket identifier |
| team_name | VARCHAR(50) | Operations team responsible for the ticket |
| status | VARCHAR(20) | Ticket status such as Closed, Open, or Pending |
| created_date | DATE | Date the ticket was created |
| closed_date | DATE | Date the ticket was closed |
| resolution_hours | DECIMAL(6,2) | Time taken to resolve the ticket in hours |
| priority | VARCHAR(20) | Priority assigned to the ticket |
{"support_tickets":[[108,"Dispatch","Closed","2024-01-14","2024-01-14",0,"Low"],[103,"Fulfillment","Closed","2024-01-28","2024-02-01",18,"Low"],[110,"Returns","Closed","2024-02-18","2024-02-20",10,"High"],[102,"Returns","Open","2024-02-05",null,null,"Medium"],[105,"Returns","Closed","2024-01-31","2024-02-02",7.25,"Medium"],[101,"Fulfillment","Closed","2024-02-03","2024-02-04",5.5,"High"],[109,"Fulfillment","Closed","2024-01-20","2024-01-21",12,"Medium"],[106,"Dispatch","Pending","2024-02-11",nulOutput[["2024-01","Dispatch","1","0.00"],["2024-01","Fulfillment","1","12.00"],["2024-02","Fulfillment","3","11.75"],["2024-02","Returns","2","8.63"],["2024-02","Dispatch","1","2.00"]]