Top Procedures by Region | Dataford Interview Questions

Write a query to find the top three medical procedures by claim volume for each region, handling ties appropriately.

Use the provided regions, procedures, and claims tables. Include only claims linked to a known region and procedure.

Output

One row per qualifying region and procedure.
Columns: region_name, procedure_name, claim_volume, and volume_rank.
Include all procedures whose rank is at most 3, including ties. Sort by region name, claim volume descending, then procedure name.

regions

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

procedures

Column	Type	Description
procedure_idPK	INT	Unique medical procedure identifier
procedure_name	VARCHAR(150)	Medical procedure name

claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
region_id	INT	Referenced region for the claim
procedure_id	INT	Referenced procedure for the claim

Your question is Top Procedures by Region. 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.