SQL for Average Treatment Effect | Dataford Interview Questions

Tempus AI is evaluating a new drug using a clinical outcomes cohort. Write a PostgreSQL query to estimate the unadjusted average treatment effect by comparing the mean post-treatment outcome for patients receiving the new drug with the mean outcome for control patients.

Requirements

Join patients, treatment assignments, and outcomes using the patient identifier.
Include only patients with a non-null post-treatment outcome.
Return treated and control patient counts, their mean outcomes, and the treatment effect, calculated as treated mean minus control mean. Round numeric results to two decimal places.

Schema

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
patient_name	VARCHAR(100)	Patient name
cancer_type	VARCHAR(50)	Cancer classification

treatment_assignments

Column	Type	Description
assignment_idPK	INT	Unique treatment assignment identifier
patient_id	INT	Assigned patient
treatment_group	VARCHAR(20)	Treatment group, either new_drug or control

outcomes

Column	Type	Description
outcome_idPK	INT	Unique outcome identifier
patient_id	INT	Patient associated with the outcome
outcome_type	VARCHAR(50)	Clinical outcome measure
outcome_score	DECIMAL(8,2)	Post-treatment outcome score

Your question is SQL for Average Treatment Effect. Start with the requirements and the three 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