Your question is Rank Customers by Premium by Region. Start with the requirements and the two 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.
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 |