SQL Window Ranking by Spend | Dataford Interview Questions

Given a table of Humana member transactions, write a SQL query using window functions to rank members by their total healthcare expenditure within each region.

Use the member_transactions table. Exclude rows with a NULL member_id or region. Treat NULL expenditure values as ignored by SUM.

Output

One row per member and region, with member_id, region, total_healthcare_expenditure, and regional_rank.
Rank members from highest to lowest total expenditure within each region, with ties sharing a rank.
Order by region, regional_rank, and member_id.

Schema

member_transactions

Column	Type	Description
transaction_idPK	INT	Unique healthcare transaction identifier
member_id	INT	Humana member identifier
region	VARCHAR(50)	Member service region
healthcare_expenditure	DECIMAL(12,2)	Amount associated with the transaction

Your question is SQL Window Ranking by Spend. Start with the requirements and the one table 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