Window Rank Customers by Premium | Dataford Interview Questions

Your question is Window Rank Customers by Premium. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Plymouth Rock Assurance wants to compare customer premium totals within each state for its Personal Lines portfolio. Write a PostgreSQL query that ranks customers by the total annual premium from their active policies.

Requirements

Sum active policy premiums for each customer, including customers with no active policies as a total of zero.
Rank customers separately within each non-null state using RANK(), with the highest total premium receiving rank 1. Customers tied on premium must share the same rank.
Return the customer name, state, total premium, and rank, ordered by state, premium descending, and customer ID as a deterministic tie-breaker.

Schema

customers

Column	Type	Description
customer_idPK	INTEGER	Unique customer identifier
customer_name	VARCHAR(100)	Customer full name
state_code	CHAR(2)	Two-letter state of residence

policies

Column	Type	Description
policy_idPK	INTEGER	Unique policy identifier
customer_id	INTEGER	Customer associated with the policy
annual_premium	NUMERIC(12,2)	Annual premium amount
policy_status	VARCHAR(20)	Current policy status