Top Members by State Claims | Dataford Interview Questions

Highmark Health analysts need a state-level view of members with the highest total claim amounts. Write a PostgreSQL query to identify the top five members in each state based on the sum of their claims.

Requirements

Join members to claims and calculate each member's total claim amount.
Rank members separately within each state using a window function.
Return only the top five members per state, ordered by state and rank. Break ties by the lower member_id.

Schema

members

Column	Type	Description
member_idPK	INTEGER	Unique member identifier
member_name	VARCHAR(100)	Member's full name
state	VARCHAR(2)	Two-letter member state code

claims

Column	Type	Description
claim_idPK	INTEGER	Unique claim identifier
member_id	INTEGER	References members.member_id
claim_date	DATE	Date the claim was submitted
claim_amount	NUMERIC(12,2)	Amount associated with the claim

Your question is Top Members by State Claims. 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.

Problem