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