Join Claims and Providers | Dataford Interview Questions

Business Context

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.

Include only claims with claim_status = 'PAID' and a service_date in calendar year 2025.
Include claims only when the related member has member_status = 'ACTIVE'.
Group results by provider specialty, labeling a missing specialty as Unknown.
Return provider_specialty and claim_count, ordered alphabetically by specialty.

members

claims

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

providers

Column	Type	Description
provider_idPK	INT	Unique provider identifier
provider_name	VARCHAR(100)	Provider or practice name
specialty	VARCHAR(80)	Provider clinical specialty

Your question is Join Claims and Providers. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.