You are asked to build a simple recurring report that tracks customer pain points over time for athenahealth support activity. Write a PostgreSQL query that returns, for each month and pain point category, the number of distinct support cases, the number of distinct affected customers, and the share of that month’s cases represented by the category. Only include cases created in 2024 that are tied to an athenaOne customer account, and exclude cases with a null pain point category. The result should be sorted by month and then by case count descending within each month.
| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Unique customer account ID |
| customer_name | VARCHAR(100) | Customer account name |
| product_surface | VARCHAR(50) | athenahealth product surface used by the customer |
| segment | VARCHAR(30) | Customer segment |
| is_active | BOOLEAN | Whether the customer account is active |
| Column | Type | Description |
|---|---|---|
| case_idPK | INT | Unique support case ID |
| customer_id | INT | Customer associated with the support case |
| created_at | DATE | Date the support case was created |
| channel | VARCHAR(30) | Support intake channel |
| severity | VARCHAR(20) | Case severity |
| Column | Type | Description |
|---|---|---|
| case_id | INT | Support case ID |
| pain_point_category | VARCHAR(50) | Normalized pain point category |
| root_cause | VARCHAR(100) | Specific root cause or issue label |