Find Largest Funnel Step Drop-Off | Dataford Interview Questions

StreamFlow tracks users moving through a signup funnel. Write a SQL query to identify the biggest drop-off between consecutive funnel steps for each funnel version.

Requirements

Consider only events where completed_flag = true.
Count distinct users who completed each step within each funnel version.
Compare each step to the immediately previous step in the same funnel version.
Return the step with the largest user drop-off per funnel version, including the previous step name, current step name, previous step users, current step users, drop_off_users, and drop_off_rate.

Schema

funnel_steps

Column	Type	Description
step_idPK	INT	Unique step identifier
funnel_version	VARCHAR(20)	Funnel version label
step_name	VARCHAR(50)	Name of the funnel step
step_order	INT	Step position within the funnel

users

Column	Type	Description
user_idPK	INT	Unique user identifier
acquisition_channel	VARCHAR(30)	Marketing acquisition source
signup_date	DATE	Date the user signed up

funnel_events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User who triggered the event
step_id	INT	Funnel step reached
event_time	TIMESTAMP	Time of the event
completed_flag	BOOLEAN	Whether the step was completed

Your question is Find Largest Funnel Step 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