Running Total and Customer Ranking | Dataford Interview Questions

Mutual of Omaha's claims analytics team needs a regional view of paid claim activity. Write a PostgreSQL query that aggregates paid claims per policyholder, calculates a regional running total, and ranks policyholders within each region.

Requirements

Include only claims with claim_status = 'Paid' and policyholders with at least one non-null claim amount.
Calculate each policyholder's total paid claims.
Calculate a running total within each region, ordered by total paid claims descending and policyholder_id ascending as a tie-breaker.
Rank policyholders within each region by total paid claims descending. Tied totals must receive the same rank.

Schema

policyholders

Column	Type	Description
policyholder_idPK	INT	Unique policyholder identifier
policyholder_name	VARCHAR(100)	Policyholder name
region	VARCHAR(30)	Geographic servicing region

claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
policyholder_id	INT	Referenced policyholder identifier
claim_date	DATE	Date the claim was recorded
claim_amount	DECIMAL(12,2)	Claim amount
claim_status	VARCHAR(20)	Claim processing status

Your question is Running Total and Customer Ranking. 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