You are given complaint records from athenaOne support operations. Write a query to calculate the monthly trend in customer complaints by category, returning one row per complaint category and month with the total number of complaints filed in that month. Use the complaint submission date to derive the month, and sort the results by month and category.
patient_complaints
| column_name | type | description |
|---|---|---|
| complaint_id | INT | Unique complaint record ID |
| customer_id | INT | Customer account identifier |
| complaint_category | VARCHAR(50) | Complaint category such as Billing or Scheduling |
| complaint_date | DATE | Date the complaint was submitted |
| complaint_status | VARCHAR(20) | Current complaint status |
| complaint_id | customer_id | complaint_category | complaint_date | complaint_status |
|---|---|---|---|---|
| 1004 | 204 | Billing | 2024-03-02 | Closed |
| 1001 | 201 | Scheduling | 2024-01-05 | Open |
| 1002 | 202 | Billing | 2024-01-12 | Closed |
| 1008 | 208 | Portal | 2024-02-28 | Open |
| 1010 | 210 | Billing | 2024-01-30 | Open |
| complaint_month | complaint_category | complaint_count |
|---|---|---|
| 2024-01 | Billing | 2 |
| 2024-01 | Scheduling | 2 |
| 2024-02 | Billing | 1 |
| 2024-02 | Portal | 2 |
| 2024-03 | Billing | 2 |
| Column | Type | Description |
|---|---|---|
| complaint_idPK | INT | Unique complaint record ID |
| customer_id | INT | Customer account identifier |
| complaint_category | VARCHAR(50) | Complaint category |
| complaint_date | DATE | Date the complaint was submitted |
| complaint_status | VARCHAR(20) | Current complaint status |
{"patient_complaints":[[1004,204,"Billing","2024-03-02","Closed"],[1001,201,"Scheduling","2024-01-05","Open"],[1007,207,"Billing","2024-02-15","Open"],[1003,203,"Scheduling","2024-01-20","Escalated"],[1009,209,"Portal","2024-02-03","Closed"],[1002,202,"Billing","2024-01-12","Closed"],[1011,211,"Scheduling",null,"Open"],[1006,206,"Portal","2024-02-28","Open"],[1005,205,"Billing","2024-03-18","Open"],[1010,210,"Billing","2024-01-30","Open"],[1012,212,null,"2024-03-25","Closed"],[1008,208,"Portal",Output[["2024-01","Billing","2"],["2024-01","Clinical Documentation","1"],["2024-01","Dashboard","1"],["2024-01","Interfaces","1"],["2024-01","Scheduling","2"],["2024-02","Billing","1"],["2024-02","Contracting","1"],["2024-02","Payments","1"],["2024-02","Portal","2"],["2024-02","Reporting","1"],["2024-02","Training","1"],["2024-03","Billing","2"],["2024-03","Patient Messaging","1"],["2024-03","Portal","2"],["2024-03","Reporting","1"],["2024-03","Task Management","1"]]