SQL Cohort and Timeline Query | Dataford Interview Questions

Your question is SQL Cohort and Timeline Query. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Business Context

Dana-Farber Cancer Institute's clinical analytics team is reviewing whether patients developed a specified condition after receiving a particular treatment.

Task

Write a PostgreSQL query to find every qualifying Treatment A and Condition B event pair. A diagnosis qualifies only when it occurs after Treatment A and no later than six calendar months after that treatment.

Requirements

Join patient, treatment, and diagnosis records using patient_id.
Restrict treatments to Treatment A and diagnoses to Condition B.
Exclude same-day and pre-treatment diagnoses, include the exact six-month boundary, and order results by patient and treatment date.
Return the patient identifiers and names, treatment date, and qualifying condition date.

Schema

patients

Column	Type	Description
patient_idPK	INTEGER	Unique patient identifier
patient_name	VARCHAR(100)	Patient's display name

treatments

Column	Type	Description
treatment_idPK	INTEGER	Unique treatment record identifier
patient_id	INTEGER	Patient receiving the treatment
treatment_name	VARCHAR(100)	Name of the treatment administered
treatment_date	DATE	Date the treatment was administered

diagnoses

Column	Type	Description
diagnosis_idPK	INTEGER	Unique diagnosis record identifier
patient_id	INTEGER	Patient receiving the diagnosis
condition_name	VARCHAR(100)	Name of the diagnosed condition
condition_date	DATE	Date the condition was diagnosed