SQL Procedure Frequency by Condition | Dataford Interview Questions

Given a database with two tables (one for actors/patients and one for conditions/procedures), write a query to calculate how often a specific procedure occurs within a group of patients diagnosed with a particular condition at Garner health.

Use Type 2 Diabetes as the condition and A1C Test as the procedure.

Output

Return one row with condition_name, procedure_name, diagnosed_patient_count, procedure_occurrences, patients_with_procedure, and occurrence_rate_pct.
Include only the specified condition and procedure.

Schema

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
patient_name	VARCHAR(100)	Patient display name
birth_date	DATE	Patient date of birth

clinical_events

Column	Type	Description
event_idPK	INT	Unique condition or procedure event identifier
patient_id	INT	Patient associated with the event
condition_name	VARCHAR(100)	Condition recorded for the patient
procedure_name	VARCHAR(100)	Procedure recorded for the patient
event_date	DATE	Date of the clinical event

Your question is SQL Procedure Frequency by Condition. 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