Rolling 30-Day Premium Average | Dataford Interview Questions

Your question is Rolling 30-Day Premium Average. 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.

Problem

Arthur J. Gallagher & needs a regional view of recent insurance premium activity. Write a PostgreSQL query that calculates the rolling 30-day average premium for each region using client and premium records.

Requirements

Join insurance_premiums to clients using client_id.
Partition results by client region and order each region chronologically by premium_date.
For every premium record, calculate the average premium from that date and the preceding 29 calendar days. Include the current premium in the rolling window.
Return region, premium date, premium amount, and the rolling average, ordered by region and date.
Do not replace a missing region with a fabricated label. PostgreSQL should retain a NULL region group.

Schema

clients

Column	Type	Description
client_idPK	INTEGER	Unique client identifier
client_name	VARCHAR(100)	Client name
region	VARCHAR(50)	Client operating region

insurance_premiums

Column	Type	Description
premium_idPK	INTEGER	Unique premium record identifier
client_id	INTEGER	References clients.client_id
premium_date	DATE	Date the premium was recorded
premium_amount	NUMERIC(12,2)	Premium amount in dollars