SQL Window Functions for Top Policies | Dataford Interview Questions

Write a SQL query using window functions to find the top three highest-value insurance policies per region at Verisk.

Use the provided regions and policies tables. Exclude policies without a policy value or without a matching region. For deterministic results, break equal-value ties by ascending policy ID.

Output

One row per qualifying policy, with region_name, policy_id, policy_number, policy_value, and policy_rank.
Include no more than three policies per region.
Order by region_name, policy_rank, then policy_id.

Schema

regions

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

policies

Column	Type	Description
policy_idPK	INT	Unique identifier for an insurance policy
policy_number	VARCHAR(30)	Business-facing policy reference
region_id	INT	Region associated with the policy
policy_value	NUMERIC(14,2)	Monetary value of the insurance policy

Your question is SQL Window Functions for Top Policies. 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