Task
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.
Schema
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 |
Sample data
| 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 |
Expected output
| 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 |
