Rolling 30-Day Claim Totals SQL | Dataford Interview Questions

Your question is Rolling 30-Day 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 a daily view of paid claim activity for active members. Write a PostgreSQL query that calculates each member's rolling 30-day claim total, including the current claim date and the preceding 29 calendar days.

Requirements

Include only members whose coverage_status is active and claims whose claim_status is paid.
Aggregate multiple paid claims for the same member and date before applying the window function.
Return the member ID, member name, claim date, daily paid claim total, and rolling 30-day claim total.
Treat a nullable claim amount as zero and order results by member ID and claim date.

Schema

members

Column	Type	Description
member_idPK	INT	Unique member identifier
member_name	VARCHAR(100)	Member's name
coverage_status	VARCHAR(20)	Coverage status, such as active or inactive
region	VARCHAR(30)	Member service region

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)	Paid claim amount
claim_status	VARCHAR(20)	Claim processing status