Your question is Rank Top Product Segments. Start with the requirements and the three 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.
CNA wants to compare product segments using policy premium and incurred loss data. Write a PostgreSQL query that ranks the top three qualifying segments by total annual premium.
policy_status = 'Active' and positive annual premium.RANK() to return all segments within the top three ranks, requiring at least two qualifying policies per segment. Sort by rank and total premium descending.| Column | Type | Description |
|---|---|---|
| product_idPK | INT | Unique CNA product identifier |
| product_name | VARCHAR(100) | CNA insurance product name |
| product_segment | VARCHAR(80) | Business segment used for ranking |
| product_line | VARCHAR(80) | Insurance product line |
| Column | Type | Description |
|---|---|---|
| policy_idPK | INT | Unique policy identifier |
| product_id | INT | Referenced product identifier |
| effective_date | DATE | Policy effective date |
| policy_status | VARCHAR(20) | Current policy status |
| annual_premium | NUMERIC(12,2) | Annual policy premium |
| Column | Type | Description |
|---|---|---|
| policy_idPK | INT | Policy identifier represented in the claim summary |
| incurred_loss | NUMERIC(12,2) | Total incurred loss for the policy |