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