Guidewire PolicyCenter needs an operational summary of active policies and their 2025 claims. Write a PostgreSQL query that produces one row per qualifying policy, including policies with no matching claims.
status = 'ACTIVE' and an effective_date on or before December 31, 2025.COMPLETED claim payments, showing zero when no completed payment exists.policy_number ascending. Use a CTE to aggregate payments before joining, preventing claim payment rows from inflating claim counts.| Column | Type | Description |
|---|---|---|
| policy_idPK | INT | Primary policy identifier |
| policy_number | VARCHAR(20) | Guidewire PolicyCenter policy number |
| insured_name | VARCHAR(100) | Named insured |
| status | VARCHAR(20) | Current policy status |
| product_line | VARCHAR(40) | Policy product line |
| effective_date | DATE | Policy effective date |
| Column | Type | Description |
|---|---|---|
| claim_idPK | INT | Primary claim identifier |
| policy_id | INT | Related policy identifier |
| reported_date | DATE | Date the claim was reported |
| status | VARCHAR(20) | Claim lifecycle status |
| Column | Type | Description |
|---|---|---|
| payment_idPK | INT | Primary payment identifier |
| claim_id | INT | Related claim identifier |
| amount | NUMERIC(12,2) | Payment amount |
| payment_status | VARCHAR(20) | Payment processing status |