Top Providers by Claim Volume | Dataford Interview Questions

HiLabs CareAlign needs a regional view of provider activity based on processed claims. Write a PostgreSQL query that identifies the top three healthcare providers by claim volume within each region.

Requirements

Join providers to regions and claims using their key relationships.
Count only claims with claim_status = 'Processed'.
Aggregate claim volume by region and provider, then rank providers within each region using a window function.
Return only the top three providers per region, ordering ties by provider_id.

Schema

regions

Column	Type	Description
region_idPK	INT	Unique region identifier
region_name	VARCHAR(50)	Name of the geographic region

providers

Column	Type	Description
provider_idPK	INT	Unique healthcare provider identifier
provider_name	VARCHAR(100)	Healthcare provider name
region_id	INT	References regions.region_id

claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
provider_id	INT	References providers.provider_id
claim_status	VARCHAR(20)	Current claim processing status

Your question is Top Providers by Claim Volume. 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.

Problem