You are given support case data from athenaOne and need to identify customers who experienced the same issue more than once within a reporting period. Write a query that returns each customer and issue type combination with more than one matching case between 2024-01-01 and 2024-03-31, inclusive. Only include cases tied to a known customer and a non-null issue type, and return the customer name, issue type, and the number of times that issue occurred during the period.
customers| column | type | description |
|---|---|---|
| customer_id | INT | Unique customer identifier |
| customer_name | VARCHAR(100) | Customer organization name |
| segment | VARCHAR(50) | Customer segment |
| go_live_date | DATE | Customer go-live date |
support_cases| column | type | description |
|---|---|---|
| case_id | INT | Unique support case identifier |
| customer_id | INT | Customer tied to the case |
| issue_type_id | INT | Issue category identifier |
| created_at | TIMESTAMP | Case creation timestamp |
| status | VARCHAR(30) | Current case status |
issue_types| column | type | description |
|---|---|---|
| issue_type_id | INT | Unique issue type identifier |
| issue_name | VARCHAR(100) | Issue category name |
| product_surface | VARCHAR(50) | athenahealth product surface |
support_cases| case_id | customer_id | issue_type_id | created_at | status |
|---|---|---|---|---|
| 1002 | 1 | 10 | 2024-02-10 09:15:00 | Closed |
| 1001 | 1 | 10 | 2024-01-05 08:30:00 | Resolved |
| 1004 | 2 | 11 | 2024-03-15 11:00:00 | Closed |
| 1005 | 2 | 11 | 2024-03-20 14:45:00 | Open |
| 1011 | 6 | 10 | 2024-02-14 10:00:00 | Closed |
| customer_name | issue_name | issue_count |
|---|---|---|
| Green Valley Clinic | Eligibility Check Failure | 2 |
| Harbor Family Practice | Claim Scrubbing Error | 2 |
| Northside Pediatrics | Eligibility Check Failure | 2 |
| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Unique customer identifier |
| customer_name | VARCHAR(100) | Customer organization name |
| segment | VARCHAR(50) | Customer segment |
| go_live_date | DATE | Date the customer started using the platform |
| Column | Type | Description |
|---|---|---|
| case_idPK | INT | Unique support case identifier |
| customer_id | INT | Customer tied to the case |
| issue_type_id | INT | Issue category identifier |
| created_at | TIMESTAMP | Case creation timestamp |
| status | VARCHAR(30) | Current support case status |
| Column | Type | Description |
|---|---|---|
| issue_type_idPK | INT | Unique issue type identifier |
| issue_name | VARCHAR(100) | Issue category name |
| product_surface | VARCHAR(50) | athenahealth product surface |
{"customers":[[3,"Harbor Family Practice","Enterprise","2022-11-15"],[1,"Northside Pediatrics","Mid-Market","2023-02-01"],[5,"Sunrise Women’s Health","SMB","2024-01-10"],[2,"Green Valley Clinic","SMB","2023-06-20"],[8,"Lakeshore Medical Group",null,"2024-03-01"],[4,"Riverside Internal Medicine","Mid-Market","2021-09-30"],[6,"Pinecrest Urgent Care","SMB","2023-12-05"],[7,"Blue Ridge Cardiology","Enterprise","2022-04-18"],["1","Meadowbrook Medical","Growth","2022-10-09"],["2","Clearwater PediatricOutput[["Green Valley Clinic","Claim Scrubbing Error","2"],["Harbor Family Practice","Patient Portal Login Issue","2"],["Northside Pediatrics","Eligibility Check Failure","2"]]