Policies Sold Per Region Query | Dataford Interview Questions

Business Context

AXA XL Insurance needs a regional view of policies sold across its underwriting portfolio. Regions with no sold policies must still appear so that reporting includes the complete regional structure.

Task

Write a PostgreSQL query that returns the number of policies with policy_status = 'Sold' for every AXA XL Insurance region.

Requirements

Include every region, including regions with zero sold policies.
Count only policies whose status is Sold.
Return region_name and the count as sold_policy_count.
Sort by sold policy count descending, then region name ascending.

Schema

regions

Column	Type	Description
region_idPK	INT	Unique region identifier
region_name	VARCHAR(100)	AXA XL Insurance reporting region

policies

Column	Type	Description
policy_idPK	INT	Unique policy identifier
policy_number	VARCHAR(30)	Business policy reference
region_id	INT	Assigned reporting region
policy_status	VARCHAR(20)	Current policy status
product_line	VARCHAR(80)	Insurance product line

Your question is Policies Sold Per Region Query. 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