Interview Guides

Analyze Signup Funnel Drop-Off | Dataford Interview Questions - Dataford - Ace your Interview

Analyze Signup Funnel Drop-Off

Medium

SQL & Data Manipulation

Asked at 1 company1JoinsAggregationsCase When

Also asked at

Problem

Task

StreamFlow wants to measure where users drop off in its onboarding funnel. Write a PostgreSQL query to calculate how many users reached each funnel step and how many dropped before the next step within a 7-day window from signup.

Requirements

Treat the funnel as: landing_view signup_started email_verified subscription_started.
Count each user at most once per step, using their earliest event timestamp for that step.
Only include users who signed up between 2024-01-01 and 2024-01-31.
A step counts only if it happened on or after signup and within 7 days of the user's signup date.
Return one row per funnel step with:
- users who reached the step
- users who dropped before the next step
- drop-off percentage from that step to the next

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_date	DATE	Date the user registered
acquisition_channel	VARCHAR(50)	Marketing source

`funnel_steps`

column	type	description
step_name	VARCHAR(50)	Funnel step name
step_order	INT	Step sequence

`events`

column	type	description
event_id	INT	Unique event identifier
user_id	INT	User who generated the event
event_name	VARCHAR(50)	Event type
event_time	TIMESTAMP	Event timestamp
device_type	VARCHAR(20)	Device used

Sample Data

`users`

user_id	signup_date	acquisition_channel
4	2024-01-20	Paid Search
1	2024-01-03	Organic Search
7	2024-02-01	Referral

`funnel_steps`

step_name	step_order
landing_view	1
signup_started	2
email_verified	3
subscription_started	4

`events`

event_id	user_id	event_name	event_time	device_type
103	1	landing_view	2024-01-03 09:00:00	web
110	2	signup_started	2024-01-06 10:00:00	mobile
118	4	subscription_started	2024-01-29 11:00:00	web

Expected Output

step_name	step_order	users_reached	users_dropped_before_next_step	drop_off_pct
landing_view	1	5	1	20.00
signup_started	2	4	1	25.00
email_verified	3	3	1	33.33
subscription_started	4	2	2	100.00

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user registered
acquisition_channel	VARCHAR(50)	Marketing acquisition source

funnel_steps

Column	Type	Description
step_namePK	VARCHAR(50)	Name of the funnel step
step_order	INT	Step sequence in the funnel

events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User who generated the event
event_name	VARCHAR(50)	Event type corresponding to a funnel step or other action
event_time	TIMESTAMP	Time the event occurred
device_type	VARCHAR(20)	Device used for the event

Sample Data

Example 1

Input

{"users":[["4","2024-01-20","Paid Search"],["1","2024-01-03","Organic Search"],["6","2024-01-28",null],["2","2024-01-05","Paid Social"],["8","2024-01-15","Organic Search"],["3","2024-01-10","Referral"],["5","2024-01-25","Email"],["7","2024-02-01","Referral"],["9","2024-01-25","Unknown"],["10","2024-01-22","Retargeting"],["11","2024-01-01","Community"],["12","2024-02-01","Email"],["13","2024-01-25","SEO"],["14","2024-01-13","Event"],["15","2024-01-27","Partner"],["16","2024-01-04","Play Store"],[

Output

[["landing_view","1","5","5","100.00"],["signup_started_15","1","0","0","0.00"],["signup_started_21","1","0","-5","0.00"],["signup_started_21","1","0","0","0.00"],["signup_started_21","1","0","0","0.00"],["signup_started_21","1","0","0","0.00"],["email_verified_10","1","0","0","0.00"],["email_verified_10","1","0","0","0.00"],["landing_view","1","5","0","0.00"],["landing_view","1","5","5","100.00"],["landing_view","1","5","5","100.00"],["email_verified_10","1","0","0","0.00"],["subscription_start

Problem

Task

Requirements

Treat the funnel as: landing_view signup_started email_verified subscription_started.
Count each user at most once per step, using their earliest event timestamp for that step.
Only include users who signed up between 2024-01-01 and 2024-01-31.
A step counts only if it happened on or after signup and within 7 days of the user's signup date.
Return one row per funnel step with:
- users who reached the step
- users who dropped before the next step
- drop-off percentage from that step to the next

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_date	DATE	Date the user registered
acquisition_channel	VARCHAR(50)	Marketing source

`funnel_steps`

column	type	description
step_name	VARCHAR(50)	Funnel step name
step_order	INT	Step sequence

`events`

column	type	description
event_id	INT	Unique event identifier
user_id	INT	User who generated the event
event_name	VARCHAR(50)	Event type
event_time	TIMESTAMP	Event timestamp
device_type	VARCHAR(20)	Device used

Sample Data

`users`

user_id	signup_date	acquisition_channel
4	2024-01-20	Paid Search
1	2024-01-03	Organic Search
7	2024-02-01	Referral

`funnel_steps`

step_name	step_order
landing_view	1
signup_started	2
email_verified	3
subscription_started	4

`events`

event_id	user_id	event_name	event_time	device_type
103	1	landing_view	2024-01-03 09:00:00	web
110	2	signup_started	2024-01-06 10:00:00	mobile
118	4	subscription_started	2024-01-29 11:00:00	web

Expected Output

step_name	step_order	users_reached	users_dropped_before_next_step	drop_off_pct
landing_view	1	5	1	20.00
signup_started	2	4	1	25.00
email_verified	3	3	1	33.33
subscription_started	4	2	2	100.00

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user registered
acquisition_channel	VARCHAR(50)	Marketing acquisition source

funnel_steps

Column	Type	Description
step_namePK	VARCHAR(50)	Name of the funnel step
step_order	INT	Step sequence in the funnel

events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User who generated the event
event_name	VARCHAR(50)	Event type corresponding to a funnel step or other action
event_time	TIMESTAMP	Time the event occurred
device_type	VARCHAR(20)	Device used for the event

Sample Data

Example 1

Input

{"users":[["4","2024-01-20","Paid Search"],["1","2024-01-03","Organic Search"],["6","2024-01-28",null],["2","2024-01-05","Paid Social"],["8","2024-01-15","Organic Search"],["3","2024-01-10","Referral"],["5","2024-01-25","Email"],["7","2024-02-01","Referral"],["9","2024-01-25","Unknown"],["10","2024-01-22","Retargeting"],["11","2024-01-01","Community"],["12","2024-02-01","Email"],["13","2024-01-25","SEO"],["14","2024-01-13","Event"],["15","2024-01-27","Partner"],["16","2024-01-04","Play Store"],[

Output

[["landing_view","1","5","5","100.00"],["signup_started_15","1","0","0","0.00"],["signup_started_21","1","0","-5","0.00"],["signup_started_21","1","0","0","0.00"],["signup_started_21","1","0","0","0.00"],["signup_started_21","1","0","0","0.00"],["email_verified_10","1","0","0","0.00"],["email_verified_10","1","0","0","0.00"],["landing_view","1","5","0","0.00"],["landing_view","1","5","5","100.00"],["landing_view","1","5","5","100.00"],["email_verified_10","1","0","0","0.00"],["subscription_start

Practice SQL

PostgreSQL

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

Up next

Find Largest Funnel Step Drop-OffMedium

Analyze Asana Onboarding Funnel DropoffMedium

Find Onboarding Funnel Drop-OffMedium

Next question

Analyze Signup Funnel Drop-Off

Medium

SQL & Data Manipulation

Asked at 1 company1JoinsAggregationsCase When

Also asked at

Problem

Task

Requirements

Treat the funnel as: landing_view signup_started email_verified subscription_started.
Count each user at most once per step, using their earliest event timestamp for that step.
Only include users who signed up between 2024-01-01 and 2024-01-31.
A step counts only if it happened on or after signup and within 7 days of the user's signup date.
Return one row per funnel step with:
- users who reached the step
- users who dropped before the next step
- drop-off percentage from that step to the next

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_date	DATE	Date the user registered
acquisition_channel	VARCHAR(50)	Marketing source

`funnel_steps`

column	type	description
step_name	VARCHAR(50)	Funnel step name
step_order	INT	Step sequence

`events`

column	type	description
event_id	INT	Unique event identifier
user_id	INT	User who generated the event
event_name	VARCHAR(50)	Event type
event_time	TIMESTAMP	Event timestamp
device_type	VARCHAR(20)	Device used

Sample Data

`users`

user_id	signup_date	acquisition_channel
4	2024-01-20	Paid Search
1	2024-01-03	Organic Search
7	2024-02-01	Referral

`funnel_steps`

step_name	step_order
landing_view	1
signup_started	2
email_verified	3
subscription_started	4

`events`

event_id	user_id	event_name	event_time	device_type
103	1	landing_view	2024-01-03 09:00:00	web
110	2	signup_started	2024-01-06 10:00:00	mobile
118	4	subscription_started	2024-01-29 11:00:00	web

Expected Output

step_name	step_order	users_reached	users_dropped_before_next_step	drop_off_pct
landing_view	1	5	1	20.00
signup_started	2	4	1	25.00
email_verified	3	3	1	33.33
subscription_started	4	2	2	100.00

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user registered
acquisition_channel	VARCHAR(50)	Marketing acquisition source

funnel_steps

Column	Type	Description
step_namePK	VARCHAR(50)	Name of the funnel step
step_order	INT	Step sequence in the funnel

events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User who generated the event
event_name	VARCHAR(50)	Event type corresponding to a funnel step or other action
event_time	TIMESTAMP	Time the event occurred
device_type	VARCHAR(20)	Device used for the event

Sample Data

Example 1

Input

{"users":[["4","2024-01-20","Paid Search"],["1","2024-01-03","Organic Search"],["6","2024-01-28",null],["2","2024-01-05","Paid Social"],["8","2024-01-15","Organic Search"],["3","2024-01-10","Referral"],["5","2024-01-25","Email"],["7","2024-02-01","Referral"],["9","2024-01-25","Unknown"],["10","2024-01-22","Retargeting"],["11","2024-01-01","Community"],["12","2024-02-01","Email"],["13","2024-01-25","SEO"],["14","2024-01-13","Event"],["15","2024-01-27","Partner"],["16","2024-01-04","Play Store"],[

Output

[["landing_view","1","5","5","100.00"],["signup_started_15","1","0","0","0.00"],["signup_started_21","1","0","-5","0.00"],["signup_started_21","1","0","0","0.00"],["signup_started_21","1","0","0","0.00"],["signup_started_21","1","0","0","0.00"],["email_verified_10","1","0","0","0.00"],["email_verified_10","1","0","0","0.00"],["landing_view","1","5","0","0.00"],["landing_view","1","5","5","100.00"],["landing_view","1","5","5","100.00"],["email_verified_10","1","0","0","0.00"],["subscription_start

Problem

Task

Requirements

Treat the funnel as: landing_view signup_started email_verified subscription_started.
Count each user at most once per step, using their earliest event timestamp for that step.
Only include users who signed up between 2024-01-01 and 2024-01-31.
A step counts only if it happened on or after signup and within 7 days of the user's signup date.
Return one row per funnel step with:
- users who reached the step
- users who dropped before the next step
- drop-off percentage from that step to the next

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_date	DATE	Date the user registered
acquisition_channel	VARCHAR(50)	Marketing source

`funnel_steps`

column	type	description
step_name	VARCHAR(50)	Funnel step name
step_order	INT	Step sequence

`events`

column	type	description
event_id	INT	Unique event identifier
user_id	INT	User who generated the event
event_name	VARCHAR(50)	Event type
event_time	TIMESTAMP	Event timestamp
device_type	VARCHAR(20)	Device used

Sample Data

`users`

user_id	signup_date	acquisition_channel
4	2024-01-20	Paid Search
1	2024-01-03	Organic Search
7	2024-02-01	Referral

`funnel_steps`

step_name	step_order
landing_view	1
signup_started	2
email_verified	3
subscription_started	4

`events`

event_id	user_id	event_name	event_time	device_type
103	1	landing_view	2024-01-03 09:00:00	web
110	2	signup_started	2024-01-06 10:00:00	mobile
118	4	subscription_started	2024-01-29 11:00:00	web

Expected Output

step_name	step_order	users_reached	users_dropped_before_next_step	drop_off_pct
landing_view	1	5	1	20.00
signup_started	2	4	1	25.00
email_verified	3	3	1	33.33
subscription_started	4	2	2	100.00

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user registered
acquisition_channel	VARCHAR(50)	Marketing acquisition source

funnel_steps

Column	Type	Description
step_namePK	VARCHAR(50)	Name of the funnel step
step_order	INT	Step sequence in the funnel

events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User who generated the event
event_name	VARCHAR(50)	Event type corresponding to a funnel step or other action
event_time	TIMESTAMP	Time the event occurred
device_type	VARCHAR(20)	Device used for the event

Sample Data

Example 1

Input

{"users":[["4","2024-01-20","Paid Search"],["1","2024-01-03","Organic Search"],["6","2024-01-28",null],["2","2024-01-05","Paid Social"],["8","2024-01-15","Organic Search"],["3","2024-01-10","Referral"],["5","2024-01-25","Email"],["7","2024-02-01","Referral"],["9","2024-01-25","Unknown"],["10","2024-01-22","Retargeting"],["11","2024-01-01","Community"],["12","2024-02-01","Email"],["13","2024-01-25","SEO"],["14","2024-01-13","Event"],["15","2024-01-27","Partner"],["16","2024-01-04","Play Store"],[

Output

[["landing_view","1","5","5","100.00"],["signup_started_15","1","0","0","0.00"],["signup_started_21","1","0","-5","0.00"],["signup_started_21","1","0","0","0.00"],["signup_started_21","1","0","0","0.00"],["signup_started_21","1","0","0","0.00"],["email_verified_10","1","0","0","0.00"],["email_verified_10","1","0","0","0.00"],["landing_view","1","5","0","0.00"],["landing_view","1","5","5","100.00"],["landing_view","1","5","5","100.00"],["email_verified_10","1","0","0","0.00"],["subscription_start

Practice SQL

PostgreSQL

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

Up next

Find Largest Funnel Step Drop-OffMedium

Analyze Asana Onboarding Funnel DropoffMedium

Find Onboarding Funnel Drop-OffMedium

Next question