Window Function Ranking Claims | Dataford Interview Questions

The Hanover Insurance Group wants to review open claims by active customer, with the highest-value claims shown first. Write a PostgreSQL query using a window function to rank each qualifying claim within its customer.

Requirements

Join customers to claims using customer_id.
Include only active customers and claims with claim_status = 'OPEN' and a non-null claim amount.
Use RANK() to rank claims by claim_amount in descending order within each customer. Tied amounts must receive the same rank.
Return the customer ID, customer name, claim ID, claim amount, and calculated rank, ordered by customer and rank.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique policyholder identifier
customer_name	VARCHAR(100)	Policyholder name
is_active	BOOLEAN	Whether the customer is currently active

claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
customer_id	INT	Customer associated with the claim
claim_amount	DECIMAL(12,2)	Reported claim amount
claim_status	VARCHAR(20)	Current claim status

Your question is Window Function Ranking 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