Your question is Join Claims and Providers. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Highmark Health's Business Analytics team needs a provider-specialty view of paid medical claims for active members.
Write a PostgreSQL query that joins members, claims, and providers and returns the number of qualifying claims for each provider specialty.
claim_status = 'PAID' and a service_date in calendar year 2025.member_status = 'ACTIVE'.Unknown.provider_specialty and claim_count, ordered alphabetically by specialty.| Column | Type | Description |
|---|---|---|
| member_idPK | INT | Unique member identifier |
| member_name | VARCHAR(100) | Member display name |
| member_status | VARCHAR(20) | Current membership status |
| Column | Type | Description |
|---|---|---|
| claim_idPK | INT | Unique claim identifier |
| member_id | INT | Member associated with the claim |
| provider_id | INT | Provider associated with the claim |
| service_date | DATE | Date on which care was provided |
| claim_status | VARCHAR(20) | Claim processing status |
| Column | Type | Description |
|---|---|---|
| provider_idPK | INT | Unique provider identifier |
| provider_name | VARCHAR(100) | Provider or practice name |
| specialty | VARCHAR(80) | Provider clinical specialty |