Members With Claims But No Enrollment | Dataford Interview Questions

Write a query to find all members who have a medical claim but do not have an active enrollment record in a specific month for Santa Clara Family Health Plan.

For this exercise, use January 2025 as the specific month. Treat an enrollment with a NULL end date as ongoing.

Output

Return one row per qualifying member with member_id and member_name.
Include only members with a medical claim in January 2025 and no active enrollment overlapping that month.
Order by member_id ascending.

Schema

members

Column	Type	Description
member_idPK	INT	Unique Santa Clara Family Health Plan member identifier
member_name	VARCHAR(100)	Member's full name

medical_claims

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

enrollments

Column	Type	Description
enrollment_idPK	INT	Unique enrollment record identifier
member_id	INT	Member associated with the enrollment
enrollment_status	VARCHAR(20)	Enrollment status
enrollment_start	DATE	Enrollment effective date
enrollment_end	DATE	Enrollment termination date, or NULL when ongoing

Your question is Members With Claims But No Enrollment. Start with the requirements and the three 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