Claims in a Thirty-Day Window | Dataford Interview Questions

Write a query to identify Blue Cross Blue Shield of Michigan members who have had more than three claims within a thirty-day window.

Treat the thirty-day window as inclusive of both boundary dates. Return one row per qualifying member, using that member's earliest qualifying window.

Output

Columns: member_id, member_name, window_start, window_end, and claims_in_window
Include only members with more than three claims in the selected window
Order by member_id ascending

Schema

members

Column	Type	Description
member_idPK	INT	Unique Blue Cross Blue Shield of Michigan member identifier
member_name	VARCHAR(120)	Member's full name

claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
member_id	INT	Member associated with the claim
claim_date	DATE	Date the claim was submitted

Your question is Claims in a Thirty-Day Window. 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