Top 3 Policies Per Region | Dataford Interview Questions

Arthur J. Gallagher & needs a regional view of its highest-grossing policies for 2025. Write a PostgreSQL query that joins policy records to their regions, ranks qualifying policies within each region, and returns the top three.

Requirements

Include only policies with status = 'Active' and an issued_on date in 2025.
Exclude policies whose annual_premium is NULL.
Use a window function partitioned by region, ranking higher premiums first. Break ties with the lowest policy_id.
Return no more than three policies per region, ordered by region name and rank.

Schema

regions

Column	Type	Description
region_idPK	INT	Unique identifier for an operating region
region_name	VARCHAR(100)	Name of the operating region

policies

Column	Type	Description
policy_idPK	INT	Unique identifier for a policy
region_id	INT	Region associated with the policy
status	VARCHAR(20)	Current policy status
issued_on	DATE	Date the policy was issued
annual_premium	NUMERIC(12,2)	Annual premium amount

Your question is Top 3 Policies Per Region. 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