Average Claims for Active Members | Dataford Interview Questions

Aetna's claims reporting team needs the average claim amount submitted for members who had maintained active coverage for at least one year when each claim was submitted. Write a PostgreSQL query using the claims and members tables.

Requirements

Join each claim to its member record.
Include only claims submitted at least one year after the member's enrollment date.
Exclude claims after a member's termination date, while treating a NULL termination date as ongoing coverage.
Return one row containing the average qualifying claim amount, rounded to two decimal places. PostgreSQL's AVG should naturally ignore NULL claim amounts.

Schema

members

Column	Type	Description
member_idPK	INT	Aetna member identifier
member_name	VARCHAR(100)	Member's name
enrollment_date	DATE	Coverage start date
termination_date	DATE	Coverage end date, or NULL for ongoing coverage

claims

Column	Type	Description
claim_idPK	INT	Claim identifier
member_id	INT	Member associated with the claim
claim_date	DATE	Date the claim was submitted
claim_amount	NUMERIC(12,2)	Billed claim amount
claim_type	VARCHAR(30)	Claim classification

Your question is Average Claims for Active Members. 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