Your question is SQL Query for Expected Results. 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.
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 |