Top Spend Categories by Region | Dataford Interview Questions

Write a SQL query to find the top three medical claim categories by spend for each region, ranking them using a window function.

Use the provided claims, members, and claim categories data. Include only claims associated with a member and category, and treat missing spend as zero when calculating category totals.

Output

One row per qualifying region and claim category, with region, category_name, total_spend, and spend_rank.
Include categories ranked 1 through 3 within each region, preserving ties.
Order by region, total_spend descending, and category_name.

Schema

claims

Column	Type	Description
claim_idPK	INT	Unique medical claim identifier
member_id	INT	Member associated with the claim
category_id	INT	Medical claim category identifier
spend	DECIMAL(12,2)	Amount paid for the claim

members

Column	Type	Description
member_idPK	INT	Unique Oscar member identifier
region	VARCHAR(50)	Member service region

claim_categories

Column	Type	Description
category_idPK	INT	Unique claim category identifier
category_name	VARCHAR(100)	Medical service category name

Your question is Top Spend Categories 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.

Problem