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