Window Functions for Claims | Dataford Interview Questions

Write a SQL query using window functions to find each patient’s most recent claim and the previous claim date for Cohere Health data.

Use the provided patients and claims tables. Include patients with at least one claim, use claim_id as the tie-breaker when claim dates match, and exclude unmatched claims.

Output

One row per patient with a claim
Columns: patient_id, patient_name, most_recent_claim_id, most_recent_claim_date, and previous_claim_date
Sort by patient_id ascending

Schema

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
patient_name	VARCHAR(100)	Patient display name

claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
patient_id	INT	Patient associated with the claim
claim_date	DATE	Date the claim was submitted
claim_status	VARCHAR(30)	Current claim processing status

Your question is Window Functions for Claims. 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