SQL Join for Mutation Frequencies | Dataford Interview Questions

Write a SQL query to join clinical patient tables with genetic test results, filtering for specific mutation frequencies across multiple cohorts.

Return qualifying results where mutation frequency is between 0.01 and 0.05, inclusive, and the mutation appears in at least two distinct cohorts.

Output

One row per qualifying patient mutation result, with patient_id, patient_name, cohort_name, mutation_code, and mutation_frequency.
Include only mutations meeting the frequency and multi-cohort requirements.
Order by mutation_code, cohort_name, and patient_id ascending.

clinical_patients

Column	Type	Description
patient_idPK	INT	Unique clinical patient identifier
patient_name	VARCHAR(100)	Patient display name
cohort_id	INT	Clinical cohort assigned to the patient
indication	VARCHAR(100)	Clinical indication associated with the patient

cohorts

Column	Type	Description
cohort_idPK	INT	Unique cohort identifier
cohort_name	VARCHAR(100)	Cohort name
study_code	VARCHAR(30)	Study code associated with the cohort

genetic_test_results

Column	Type	Description
result_idPK	INT	Unique genetic test result identifier
patient_id	INT	Patient associated with the test result
mutation_code	VARCHAR(30)	Mutation identifier
mutation_frequency	DECIMAL(6,5)	Observed mutation frequency
test_date	DATE	Date the genetic test was performed

Your question is SQL Join for Mutation Frequencies. 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.