Running Totals With SQL Window Functions | Dataford Interview Questions

Munich Re needs a regional view of policy claims for portfolio monitoring. Write a PostgreSQL query that calculates monthly eligible claims and a rolling twelve-month total for each region.

Requirements

Join policies to claims using the policy identifier.
Include only claims with a status of Paid or Approved, and exclude policies without a region.
Aggregate eligible claim amounts by region and calendar month.
Use a window function to calculate the total for the current month and the preceding eleven months. Return the results ordered by region and month.

Schema

policies

Column	Type	Description
policy_idPK	INTEGER	Unique policy identifier
policy_number	VARCHAR(20)	Munich Re policy reference
region	VARCHAR(40)	Geographic region assigned to the policy
inception_date	DATE	Policy start date

claims

Column	Type	Description
claim_idPK	INTEGER	Unique claim identifier
policy_id	INTEGER	Identifier of the related policy
claim_date	DATE	Date the claim was recorded
claim_amount	NUMERIC(12,2)	Claim amount in euros
claim_status	VARCHAR(20)	Current processing status of the claim

Your question is Running Totals With SQL Window Functions. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem