Interview Guides

Find Largest Funnel Step Drop-Off | Dataford Interview Questions - Dataford - Ace your Interview

Find Largest Funnel Step Drop-Off

Medium

SQL & Data Manipulation

Asked at 6 companies6Window FunctionsCase WhenFunnel Analysis

Also asked at

Problem

Task

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.

Table Definitions

`funnel_steps`

column	type	description
step_id	INT	Unique step identifier
funnel_version	VARCHAR(20)	Funnel version
step_name	VARCHAR(50)	Step label
step_order	INT	Step sequence in the funnel

`users`

column	type	description
user_id	INT	Unique user identifier
acquisition_channel	VARCHAR(30)	Marketing source
signup_date	DATE	User signup date

`funnel_events`

column	type	description
event_id	INT	Unique event identifier
user_id	INT	User who triggered the event
step_id	INT	Funnel step reached
event_time	TIMESTAMP	Event timestamp
completed_flag	BOOLEAN	Whether the step was completed

Sample Data

Representative rows are provided below in the structured sample data section.

Expected Output

funnel_version	previous_step	current_step	previous_step_users	current_step_users	drop_off_users	drop_off_rate
v1	verify_email	add_payment	3	1	2	0.6667
v2	create_account	choose_plan	3	1	2	0.6667

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

Sample Data

Example 1

Input

{"users":[["101","Organic Search","2024-04-01"],["102","Paid Social","2024-04-01"],["103","Referral","2024-04-02"],["104","Email","2024-04-02"],["105","Organic Search","2024-04-03"],["106",null,"2024-04-03"],["107","Affiliate","2024-04-04"],["108","Paid Search","2024-04-04"],["109","","2024-04-05"],["110","Podcast","2024-04-05"],["111","null","2024-04-01"],["112","Push Notification","2024-04-06"],["113","Paid Search","2024-04-03"],["114","Instagram","2024-04-01"],["115","Affiliate","2024-04-08"]

Output

[["experiment_13","address_entry","download_app","0","0","0","null"],["fall_test","complete_profile","first_purchase","0","0","0","null"],["mobile_v1","choose_plan","create_account","0","0","0","null"],["v1","verify_email","landing_page","3","0","3","1.0000"],["v2","create_account","choose_plan","3","1","2","0.6667"]]

Problem

Task

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.

Table Definitions

`funnel_steps`

column	type	description
step_id	INT	Unique step identifier
funnel_version	VARCHAR(20)	Funnel version
step_name	VARCHAR(50)	Step label
step_order	INT	Step sequence in the funnel

`users`

column	type	description
user_id	INT	Unique user identifier
acquisition_channel	VARCHAR(30)	Marketing source
signup_date	DATE	User signup date

`funnel_events`

column	type	description
event_id	INT	Unique event identifier
user_id	INT	User who triggered the event
step_id	INT	Funnel step reached
event_time	TIMESTAMP	Event timestamp
completed_flag	BOOLEAN	Whether the step was completed

Sample Data

Representative rows are provided below in the structured sample data section.

Expected Output

funnel_version	previous_step	current_step	previous_step_users	current_step_users	drop_off_users	drop_off_rate
v1	verify_email	add_payment	3	1	2	0.6667
v2	create_account	choose_plan	3	1	2	0.6667

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

Sample Data

Example 1

Input

{"users":[["101","Organic Search","2024-04-01"],["102","Paid Social","2024-04-01"],["103","Referral","2024-04-02"],["104","Email","2024-04-02"],["105","Organic Search","2024-04-03"],["106",null,"2024-04-03"],["107","Affiliate","2024-04-04"],["108","Paid Search","2024-04-04"],["109","","2024-04-05"],["110","Podcast","2024-04-05"],["111","null","2024-04-01"],["112","Push Notification","2024-04-06"],["113","Paid Search","2024-04-03"],["114","Instagram","2024-04-01"],["115","Affiliate","2024-04-08"]

Output

[["experiment_13","address_entry","download_app","0","0","0","null"],["fall_test","complete_profile","first_purchase","0","0","0","null"],["mobile_v1","choose_plan","create_account","0","0","0","null"],["v1","verify_email","landing_page","3","0","3","1.0000"],["v2","create_account","choose_plan","3","1","2","0.6667"]]

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Analyze Signup Funnel Drop-OffMedium

Find Onboarding Funnel Drop-OffMedium Measure Event Funnel Drop-OffMedium

Next question

Find Largest Funnel Step Drop-Off

Medium

SQL & Data Manipulation

Asked at 6 companies6Window FunctionsCase WhenFunnel Analysis

Also asked at

Problem

Task

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.

Table Definitions

`funnel_steps`

column	type	description
step_id	INT	Unique step identifier
funnel_version	VARCHAR(20)	Funnel version
step_name	VARCHAR(50)	Step label
step_order	INT	Step sequence in the funnel

`users`

column	type	description
user_id	INT	Unique user identifier
acquisition_channel	VARCHAR(30)	Marketing source
signup_date	DATE	User signup date

`funnel_events`

column	type	description
event_id	INT	Unique event identifier
user_id	INT	User who triggered the event
step_id	INT	Funnel step reached
event_time	TIMESTAMP	Event timestamp
completed_flag	BOOLEAN	Whether the step was completed

Sample Data

Representative rows are provided below in the structured sample data section.

Expected Output

funnel_version	previous_step	current_step	previous_step_users	current_step_users	drop_off_users	drop_off_rate
v1	verify_email	add_payment	3	1	2	0.6667
v2	create_account	choose_plan	3	1	2	0.6667

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

Sample Data

Example 1

Input

{"users":[["101","Organic Search","2024-04-01"],["102","Paid Social","2024-04-01"],["103","Referral","2024-04-02"],["104","Email","2024-04-02"],["105","Organic Search","2024-04-03"],["106",null,"2024-04-03"],["107","Affiliate","2024-04-04"],["108","Paid Search","2024-04-04"],["109","","2024-04-05"],["110","Podcast","2024-04-05"],["111","null","2024-04-01"],["112","Push Notification","2024-04-06"],["113","Paid Search","2024-04-03"],["114","Instagram","2024-04-01"],["115","Affiliate","2024-04-08"]

Output

[["experiment_13","address_entry","download_app","0","0","0","null"],["fall_test","complete_profile","first_purchase","0","0","0","null"],["mobile_v1","choose_plan","create_account","0","0","0","null"],["v1","verify_email","landing_page","3","0","3","1.0000"],["v2","create_account","choose_plan","3","1","2","0.6667"]]

Problem

Task

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.

Table Definitions

`funnel_steps`

column	type	description
step_id	INT	Unique step identifier
funnel_version	VARCHAR(20)	Funnel version
step_name	VARCHAR(50)	Step label
step_order	INT	Step sequence in the funnel

`users`

column	type	description
user_id	INT	Unique user identifier
acquisition_channel	VARCHAR(30)	Marketing source
signup_date	DATE	User signup date

`funnel_events`

column	type	description
event_id	INT	Unique event identifier
user_id	INT	User who triggered the event
step_id	INT	Funnel step reached
event_time	TIMESTAMP	Event timestamp
completed_flag	BOOLEAN	Whether the step was completed

Sample Data

Representative rows are provided below in the structured sample data section.

Expected Output

funnel_version	previous_step	current_step	previous_step_users	current_step_users	drop_off_users	drop_off_rate
v1	verify_email	add_payment	3	1	2	0.6667
v2	create_account	choose_plan	3	1	2	0.6667

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

Sample Data

Example 1

Input

{"users":[["101","Organic Search","2024-04-01"],["102","Paid Social","2024-04-01"],["103","Referral","2024-04-02"],["104","Email","2024-04-02"],["105","Organic Search","2024-04-03"],["106",null,"2024-04-03"],["107","Affiliate","2024-04-04"],["108","Paid Search","2024-04-04"],["109","","2024-04-05"],["110","Podcast","2024-04-05"],["111","null","2024-04-01"],["112","Push Notification","2024-04-06"],["113","Paid Search","2024-04-03"],["114","Instagram","2024-04-01"],["115","Affiliate","2024-04-08"]

Output

[["experiment_13","address_entry","download_app","0","0","0","null"],["fall_test","complete_profile","first_purchase","0","0","0","null"],["mobile_v1","choose_plan","create_account","0","0","0","null"],["v1","verify_email","landing_page","3","0","3","1.0000"],["v2","create_account","choose_plan","3","1","2","0.6667"]]

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Analyze Signup Funnel Drop-OffMedium

Find Onboarding Funnel Drop-OffMedium Measure Event Funnel Drop-OffMedium

Next question