The Hartford wants to compare customer premium contribution across its regional markets. Write a PostgreSQL query that aggregates posted policy premiums by customer and ranks customers within each region.
posted status.NULL premium amounts as zero and exclude customers whose region is unknown.RANK() partitioned by region, with highest totals ranked first. Preserve ties and sort the final output by region, rank, and customer ID.| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Unique customer identifier |
| customer_name | VARCHAR(100) | Customer's full name |
| region | VARCHAR(50) | Regional market assigned to the customer |
| Column | Type | Description |
|---|---|---|
| transaction_idPK | INT | Unique policy transaction identifier |
| customer_id | INT | Customer associated with the transaction |
| premium_amount | NUMERIC(12,2) | Premium amount contributed by the transaction |
| transaction_status | VARCHAR(20) | Current transaction status |