Claims Window SQL Query | Dataford Interview Questions

Write a SQL query to identify Oscar Insurance members who have had more than three medical claims within a 30-day window, including the total cost of those claims.

Treat the 30-day window as inclusive. Return only the earliest qualifying window for each member. Claims with a NULL claim type are not medical claims, and NULL costs contribute zero to the total.

Output

One row per member with a qualifying window, including member_id, member_name, window_start, window_end, claim_count, and total_cost.
Include only windows with more than three medical claims.
Order by window_start ascending, then member_id ascending.

Schema

members

Column	Type	Description
member_idPK	INTEGER	Unique Oscar Insurance member identifier
member_name	VARCHAR(100)	Member's full name

medical_claims

Column	Type	Description
claim_idPK	INTEGER	Unique claim identifier
member_id	INTEGER	Member associated with the claim
claim_date	DATE	Date the claim was submitted
claim_type	VARCHAR(100)	Claim category
total_cost	NUMERIC(12,2)	Total allowed cost of the claim

Your question is Claims Window SQL Query. 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