SQL for Enrollment Funnel Drop-Off | Dataford Interview Questions

Write a SQL query to identify patient drop-off points in a multi-stage enrollment funnel at Amgen.

Use the provided patient, enrollment status, and funnel stage data. Identify stages where fewer patients reached the stage than reached the immediately preceding stage, including the number and percentage lost.

Output

One row per stage with a drop-off.
Include stage_order, stage_name, patients_completed, previous_stage_completed, drop_off_count, and drop_off_rate_pct.
Exclude the first stage and stages without a drop-off. Order by stage_order ascending.

Schema

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
patient_name	VARCHAR(100)	Patient display name

enrollment_status

Column	Type	Description
enrollment_idPK	INT	Unique enrollment status record
patient_id	INT	Patient associated with the enrollment
current_stage_order	INT	Highest funnel stage reached by the patient
status	VARCHAR(30)	Current enrollment status

funnel_stages

Column	Type	Description
stage_orderPK	INT	Sequential order of the funnel stage
stage_name	VARCHAR(100)	Name of the enrollment stage

Your question is SQL for Enrollment Funnel Drop-Off. 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