SQL for Patient Cohorts | Dataford Interview Questions

Given a dataset of patient claims and outcomes, how would you write a SQL query to identify patient cohorts with high readmission rates?

Assume each claim belongs to a named cohort and may have a recorded 30-day readmission outcome. Include cohorts with at least 3 claims and a readmission rate of at least 50%.

Output

One row per qualifying cohort.
Columns: cohort_name, total_claims, readmissions, and readmission_rate.
Sort by readmission_rate descending, then cohort_name ascending.

Schema

patient_claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
patient_id	INT	Patient associated with the claim
cohort_name	VARCHAR(50)	Patient cohort assigned to the claim
claim_date	DATE	Date of the claim
claim_type	VARCHAR(30)	Type of claim or admission

claim_outcomes

Column	Type	Description
outcome_idPK	INT	Unique outcome identifier
claim_id	INT	Claim evaluated by the outcome
readmitted_30d	BOOLEAN	Whether the patient was readmitted within 30 days
outcome_date	DATE	Date the outcome was recorded

Your question is SQL for Patient Cohorts. 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