Time to First Clinical Claim | Dataford Interview Questions

Given a schema with member enrollments and medical claims, write a query to calculate the average time elapsed between a member's enrollment date and their first clinical claim.

Treat claims dated on or after enrollment as eligible clinical claims, and exclude members without one. Use the provided tables and return the average elapsed time in days.

Output

One row with the column average_days_elapsed
Include eligible members only and return the average across members
Return the result rounded to two decimal places

Schema

member_enrollments

Column	Type	Description
member_idPK	INT	Unique Oscar member identifier
enrollment_date	DATE	Date the member's coverage began

medical_claims

Column	Type	Description
claim_idPK	INT	Unique medical claim identifier
member_id	INT	Member associated with the claim
claim_date	DATE	Date the claim was submitted or incurred
claim_type	VARCHAR(30)	Claim category, such as clinical, dental, or pharmacy

Your question is Time to First Clinical Claim. 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