You are given support ticket data from athenahealth practices using athenaOne. Write a PostgreSQL query that returns the most common customer issues for each practice, ranked by ticket volume. Only include tickets created in January 2024 with a status other than closed. If two issue types have the same ticket count within a practice, they should receive the same rank. Return the practice name, issue category, ticket count, and rank, ordered by practice and rank.
practices| column | type | description |
|---|---|---|
| practice_id | INT | Unique practice identifier |
| practice_name | VARCHAR(100) | Practice name |
| region | VARCHAR(50) | Practice region |
issue_categories| column | type | description |
|---|---|---|
| category_id | INT | Unique issue category identifier |
| category_name | VARCHAR(100) | Issue category name |
| severity | VARCHAR(20) | Default severity label |
support_tickets| column | type | description |
|---|---|---|
| ticket_id | INT | Unique support ticket identifier |
| practice_id | INT | Practice that opened the ticket |
| category_id | INT | Issue category linked to the ticket |
| created_at | DATE | Ticket creation date |
| status | VARCHAR(20) | Current ticket status |
support_tickets| ticket_id | practice_id | category_id | created_at | status |
|---|---|---|---|---|
| 201 | 1 | 10 | 2024-01-03 | open |
| 202 | 1 | 10 | 2024-01-05 | in_progress |
| 203 | 1 | 11 | 2024-01-06 | open |
| 207 | 2 | 12 | 2024-01-08 | open |
| 210 | 3 | 13 | 2024-01-15 | open |
| 211 | 3 | NULL | 2024-01-18 | open |
| practice_name | category_name | ticket_count | issue_rank |
|---|---|---|---|
| Harbor Family Medicine | Eligibility Check | 2 | 1 |
| Harbor Family Medicine | Claim Rejection | 1 | 2 |
| Northside Pediatrics | Patient Portal Access | 2 | 1 |
| Northside Pediatrics | Scheduling | 2 | 1 |
| Summit Internal Medicine | ePrescribing | 2 | 1 |
| Summit Internal Medicine | Eligibility Check | 1 | 2 |
| Column | Type | Description |
|---|---|---|
| practice_idPK | INT | Unique identifier for a medical practice |
| practice_name | VARCHAR(100) | Display name of the practice |
| region | VARCHAR(50) | Geographic region for the practice |
| Column | Type | Description |
|---|---|---|
| category_idPK | INT | Unique identifier for an issue category |
| category_name | VARCHAR(100) | Issue category label |
| severity | VARCHAR(20) | Default severity associated with the issue category |
| Column | Type | Description |
|---|---|---|
| ticket_idPK | INT | Unique support ticket identifier |
| practice_id | INT | Practice that submitted the ticket |
| category_id | INT | Issue category associated with the ticket |
| created_at | DATE | Date the ticket was created |
| status | VARCHAR(20) | Current workflow status of the ticket |
{"practices":[[2,"Northside Pediatrics","Midwest"],[1,"Harbor Family Medicine","Northeast"],[4,"Lakeside Cardiology",null],[3,"Summit Internal Medicine","South"],[5,"Riverbend Clinic","West"],[6,"Cedar Women's Health","Northeast"],[7,"Maple Primary Care","Midwest"],[8,"Westbrook Urgent Care","South"],["1","Heritage Medical Associates","Central"],["2","Parkside Internal Medicine","Coastal"],["3","Parkside Internal Medicine","Northeast"],["4","Clearwater Family Care","New England"],["5","Cedar WomOutput[["Harbor Family Medicine","Eligibility Check","2","1"],["Harbor Family Medicine","Claim Rejection","1","2"],["Harbor Family Medicine","Patient Portal Access","1","3"],["Maple Primary Care","Eligibility Check","1","1"],["Northside Pediatrics","Patient Portal Access","2","1"],["Northside Pediatrics","Scheduling","2","2"],["Summit Internal Medicine","ePrescribing","2","1"],["Summit Internal Medicine","Eligibility Check","1","2"]]