Top Claims by Specialty SQL | Dataford Interview Questions

Given two tables of claims and providers, write a SQL query to find providers with the highest average claim amount by specialty at Garner health.

Use non-null specialties and claim amounts, and include every provider tied for the highest average within a specialty.

Output

One row per qualifying provider, with specialty, provider_id, provider_name, average_claim_amount, and specialty_rank.
Include only providers whose average is highest within their specialty.
Order by specialty ascending, then provider_id ascending.

providers

Column	Type	Description
provider_idPK	INT	Unique identifier for the provider
provider_name	VARCHAR(100)	Provider's display name
specialty	VARCHAR(80)	Provider specialty

claims

Column	Type	Description
claim_idPK	INT	Unique identifier for the claim
provider_id	INT	Provider associated with the claim
claim_amount	NUMERIC(10,2)	Billed claim amount

Your question is Top Claims by Specialty SQL. Start with the requirements and the two 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.