SQL for Healthcare Analytics | Dataford Interview Questions

Tell me about your experience writing SQL queries for healthcare scenarios at Oscar Health, including how you handle joins, filters, and aggregations.

Using the supplied member, claim, and provider data, return a claims summary for active members and qualifying 2025 paid claims.

Output

One row per active member, including members without qualifying claims.
Return member_id, member_name, state, paid_claim_count, total_paid_amount, distinct_provider_count, and spend_band.
Order by total_paid_amount descending, then member_id ascending.

Schema

members

Column	Type	Description
member_idPK	INT	Unique member identifier
member_name	VARCHAR(100)	Member display name
state	VARCHAR(2)	Member state abbreviation
is_active	BOOLEAN	Whether the member is currently active

claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
member_id	INT	Referenced member identifier
provider_id	INT	Referenced provider identifier
service_date	DATE	Date healthcare service was provided
claim_status	VARCHAR(20)	Claim processing status
paid_amount	DECIMAL(12,2)	Amount paid for the claim

providers

Column	Type	Description
provider_idPK	INT	Unique provider identifier
provider_name	VARCHAR(120)	Provider or facility name
specialty	VARCHAR(80)	Provider clinical specialty

Your question is SQL for Healthcare Analytics. 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