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.
customers| column | type | description |
|---|---|---|
| customer_id | INT | Unique customer account ID |
| customer_name | VARCHAR(100) | Customer account name |
| product_surface | VARCHAR(50) | athenahealth product surface |
| segment | VARCHAR(30) | Customer segment |
| is_active | BOOLEAN | Whether the customer is active |
support_cases| column | type | description |
|---|---|---|
| case_id | INT | Unique support case ID |
| customer_id | INT | Customer tied to the case |
| created_at | DATE | Case creation date |
| channel | VARCHAR(30) | Intake channel |
| severity | VARCHAR(20) | Case severity |
case_pain_points| column | type | description |
|---|---|---|
| case_id | INT | Support case ID |
| pain_point_category | VARCHAR(50) | Normalized pain point category |
| root_cause | VARCHAR(100) | More specific issue type |
customers| customer_id | customer_name | product_surface | segment | is_active |
|---|---|---|---|---|
| 101 | Northside Family Clinic | athenaOne | SMB | true |
| 102 | Harbor Medical Group | athenaOne | Mid-Market | true |
| 103 | Summit Pediatrics | athenaOne | Enterprise | true |
support_cases| case_id | customer_id | created_at | channel | severity |
|---|---|---|---|---|
| 1002 | 102 | 2024-01-05 | Phone | High |
| 1001 | 101 | 2024-01-12 | Portal | Medium |
| 1004 | 103 | 2024-02-03 | Chat | Low |
case_pain_points| case_id | pain_point_category | root_cause |
|---|---|---|
| 1001 | Billing | Claim edit confusion |
| 1002 | Scheduling | Template setup |
| 1004 | Billing | ERA posting delay |
| report_month | pain_point_category | case_count | affected_customers | pct_of_month_cases |
|---|---|---|---|---|
| 2024-01 | Billing | 2 | 2 | 50.00 |
| 2024-01 | Scheduling | 1 | 1 | 25.00 |
| 2024-01 | Reporting | 1 | 1 | 25.00 |
| 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 |
{"customers":[[101,"Northside Family Clinic","athenaOne","SMB",true],[102,"Harbor Medical Group","athenaOne","Mid-Market",true],[103,"Summit Pediatrics","athenaOne","Enterprise",true],[104,"Lakeside Internal Medicine","athenaOne","SMB",false],[105,"Cedar Women's Health","athenaCollector","SMB",true],[106,"River Valley Primary Care","athenaOne",null,true],[107,"Metro Specialty Partners","athenaClinicals","Enterprise",true],[108,"Blue Ridge Health","athenaOne","Mid-Market",true],[109,"West End UrgOutput[["2024-01","Billing","2","1","50.00"],["2024-01","Reporting","1","1","25.00"],["2024-01","Scheduling","1","1","25.00"],["2024-02","Billing","1","1","50.00"],["2024-02","Reporting","1","1","50.00"],["2024-03","Reporting","1","1","50.00"],["2024-03","Scheduling","1","1","50.00"]]