Rolling Member Claim Totals SQL | Dataford Interview Questions

Your question is Rolling Member Claim Totals SQL. 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

Aetna's member analytics team needs monthly claim trend metrics for members. Write a PostgreSQL query that aggregates claim amounts by member and calendar month, then calculates both cumulative claim totals and a rolling three-month average.

Requirements

Include claims from January 1, 2025 through June 30, 2025.
Aggregate each member's claims by month, treating NULL claim amounts as zero.
Calculate a running total ordered chronologically for each member.
Calculate the average monthly claim amount for the current month and the preceding two calendar months using a window frame. Return one row for each member-month with at least one claim.

Schema

members

Column	Type	Description
member_idPK	INT	Unique member identifier
member_name	VARCHAR(100)	Member's full name
plan_type	VARCHAR(30)	Aetna health plan type

claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
member_id	INT	Member associated with the claim
claim_date	DATE	Date the claim was submitted
claim_amount	NUMERIC(12,2)	Allowed claim amount