Your question is Running Totals and Moving Averages. 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.
CNA's claims analytics team needs monthly monitoring of incurred policy claims. Write a PostgreSQL query that calculates a cumulative claim total and a rolling twelve-month average by line of business.
claims to policies using policy_id.| Column | Type | Description |
|---|---|---|
| policy_idPK | INTEGER | Primary key for the policy |
| policy_number | VARCHAR(20) | CNA policy identifier |
| line_of_business | VARCHAR(40) | Policy business segment |
| effective_date | DATE | Policy effective date |
| Column | Type | Description |
|---|---|---|
| claim_idPK | INTEGER | Primary key for the claim |
| policy_id | INTEGER | Referenced policy |
| claim_date | DATE | Date the claim was incurred |
| claim_amount | NUMERIC(12,2) | Incurred claim amount |