Interview Guides

Find Onboarding Funnel Drop-Off

Medium

SQL & Data Manipulation

Asked at 2 companies2AggregationsWindow FunctionsDate Functions

Also asked at

Problem

Task

NovaPay wants to identify the largest drop-off point in its onboarding funnel. Write a PostgreSQL query to measure how many users reached each funnel step and calculate the drop-off to the next step.

Requirements

Consider only users from the web signup source who signed up in January 2024.
Use the funnel order defined in onboarding_steps.
For each step, return the number of distinct users who completed it, the number who completed the next step, the drop-off count, and the drop-off rate.
Return all steps except the final step, ordered by the largest drop-off count first.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_date	DATE	User signup date
signup_source	VARCHAR(50)	Acquisition source
country_code	VARCHAR(2)	User country

`onboarding_steps`

column	type	description
step_id	INT	Funnel step identifier
step_name	VARCHAR(50)	Funnel step name
step_order	INT	Step sequence in the funnel

`user_onboarding_events`

column	type	description
event_id	INT	Event identifier
user_id	INT	User who triggered the event
step_id	INT	Completed onboarding step
completed_at	TIMESTAMP	Completion time
device_type	VARCHAR(20)	Device used for the event

Sample Data

`users`

user_id	signup_date	signup_source	country_code
4	2024-01-20	web	GB
1	2024-01-03	web	US
7	2024-01-10	partner	US
2	2024-01-05	web	CA

`onboarding_steps`

step_id	step_name	step_order
10	Account Created	1
20	Email Verified	2
30	Profile Completed	3
40	Payment Added	4
50	First Transaction	5

`user_onboarding_events`

event_id	user_id	step_id	completed_at	device_type
1004	2	10	2024-01-05 09:00:00	mobile
1001	1	10	2024-01-03 08:00:00	desktop
1002	1	20	2024-01-03 08:10:00	desktop
1003	1	30	2024-01-03 08:25:00	desktop

Expected Output

step_name	users_at_step	users_at_next_step	drop_off_count	drop_off_rate
Profile Completed	4	1	3	0.75
Email Verified	6	4	2	0.33
Account Created	7	6	1	0.14
Payment Added	1	1	0	0.00

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
signup_source	VARCHAR(50)	Marketing or acquisition source
country_code	VARCHAR(2)	Two-letter country code

onboarding_steps

Column	Type	Description
step_idPK	INT	Unique onboarding step identifier
step_name	VARCHAR(50)	Name of the onboarding step
step_order	INT	Sequence order of the step in the funnel

user_onboarding_events

Column	Type	Description
event_idPK	INT	Unique onboarding event identifier
user_id	INT	User who completed the step
step_id	INT	Onboarding step completed
completed_at	TIMESTAMP	Timestamp when the step was completed
device_type	VARCHAR(20)	Device used for the onboarding event

Sample Data

Example 1

Input

{"users":[[4,"2024-01-20","web","GB"],[1,"2024-01-03","web","US"],[7,"2024-01-10","partner","US"],[2,"2024-01-05","web","CA"],[9,"2024-02-01","web","US"],[5,"2024-01-25","web",null],[3,"2024-01-08","web","US"],[8,"2024-01-28",null,"DE"],[6,"2023-12-30","web","FR"],[10,"2024-01-15","mobile_app","BR"],[11,"2024-01-31","web","IN"],[12,"2024-01-12","web","US"],["1","2024-01-24","comparison_site","IE"],["2","2024-02-01","sales_outreach","MX"],["3","2024-01-08","web","NL"],["4","2023-12-28","reseller"

Output

[["Account Created","7","0","7","1.00"],["Profile Completed","4","1","3","0.75"],["Email Verified","6","4","2","0.33"],["Account Created","7","6","1","0.14"],["Payment Added","1","0","1","1.00"],["First Transaction","1","0","1","1.00"],["Payment Added","1","1","0","0.00"],["Team Invited","0","0","0","0"],["Referral Sent","0","0","0","0"],["KYC Approved","0","0","0","0"],["Card Ordered","0","0","0","0"],["Debit Card Activated","0","0","0","0"],["Support Chat Opened","0","0","0","0"],["Security Qu

Problem

Task

NovaPay wants to identify the largest drop-off point in its onboarding funnel. Write a PostgreSQL query to measure how many users reached each funnel step and calculate the drop-off to the next step.

Requirements

Consider only users from the web signup source who signed up in January 2024.
Use the funnel order defined in onboarding_steps.
For each step, return the number of distinct users who completed it, the number who completed the next step, the drop-off count, and the drop-off rate.
Return all steps except the final step, ordered by the largest drop-off count first.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_date	DATE	User signup date
signup_source	VARCHAR(50)	Acquisition source
country_code	VARCHAR(2)	User country

`onboarding_steps`

column	type	description
step_id	INT	Funnel step identifier
step_name	VARCHAR(50)	Funnel step name
step_order	INT	Step sequence in the funnel

`user_onboarding_events`

column	type	description
event_id	INT	Event identifier
user_id	INT	User who triggered the event
step_id	INT	Completed onboarding step
completed_at	TIMESTAMP	Completion time
device_type	VARCHAR(20)	Device used for the event

Sample Data

`users`

user_id	signup_date	signup_source	country_code
4	2024-01-20	web	GB
1	2024-01-03	web	US
7	2024-01-10	partner	US
2	2024-01-05	web	CA

`onboarding_steps`

step_id	step_name	step_order
10	Account Created	1
20	Email Verified	2
30	Profile Completed	3
40	Payment Added	4
50	First Transaction	5

`user_onboarding_events`

event_id	user_id	step_id	completed_at	device_type
1004	2	10	2024-01-05 09:00:00	mobile
1001	1	10	2024-01-03 08:00:00	desktop
1002	1	20	2024-01-03 08:10:00	desktop
1003	1	30	2024-01-03 08:25:00	desktop

Expected Output

step_name	users_at_step	users_at_next_step	drop_off_count	drop_off_rate
Profile Completed	4	1	3	0.75
Email Verified	6	4	2	0.33
Account Created	7	6	1	0.14
Payment Added	1	1	0	0.00

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
signup_source	VARCHAR(50)	Marketing or acquisition source
country_code	VARCHAR(2)	Two-letter country code

onboarding_steps

Column	Type	Description
step_idPK	INT	Unique onboarding step identifier
step_name	VARCHAR(50)	Name of the onboarding step
step_order	INT	Sequence order of the step in the funnel

user_onboarding_events

Column	Type	Description
event_idPK	INT	Unique onboarding event identifier
user_id	INT	User who completed the step
step_id	INT	Onboarding step completed
completed_at	TIMESTAMP	Timestamp when the step was completed
device_type	VARCHAR(20)	Device used for the onboarding event

Sample Data

Example 1

Input

{"users":[[4,"2024-01-20","web","GB"],[1,"2024-01-03","web","US"],[7,"2024-01-10","partner","US"],[2,"2024-01-05","web","CA"],[9,"2024-02-01","web","US"],[5,"2024-01-25","web",null],[3,"2024-01-08","web","US"],[8,"2024-01-28",null,"DE"],[6,"2023-12-30","web","FR"],[10,"2024-01-15","mobile_app","BR"],[11,"2024-01-31","web","IN"],[12,"2024-01-12","web","US"],["1","2024-01-24","comparison_site","IE"],["2","2024-02-01","sales_outreach","MX"],["3","2024-01-08","web","NL"],["4","2023-12-28","reseller"

Output

[["Account Created","7","0","7","1.00"],["Profile Completed","4","1","3","0.75"],["Email Verified","6","4","2","0.33"],["Account Created","7","6","1","0.14"],["Payment Added","1","0","1","1.00"],["First Transaction","1","0","1","1.00"],["Payment Added","1","1","0","0.00"],["Team Invited","0","0","0","0"],["Referral Sent","0","0","0","0"],["KYC Approved","0","0","0","0"],["Card Ordered","0","0","0","0"],["Debit Card Activated","0","0","0","0"],["Support Chat Opened","0","0","0","0"],["Security Qu

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 Signup Funnel Drop-OffMedium

Analyze Asana Onboarding Funnel DropoffMedium

Next question

Find Onboarding Funnel Drop-Off

Medium

SQL & Data Manipulation

Asked at 2 companies2AggregationsWindow FunctionsDate Functions

Also asked at

Problem

Task

NovaPay wants to identify the largest drop-off point in its onboarding funnel. Write a PostgreSQL query to measure how many users reached each funnel step and calculate the drop-off to the next step.

Requirements

Consider only users from the web signup source who signed up in January 2024.
Use the funnel order defined in onboarding_steps.
For each step, return the number of distinct users who completed it, the number who completed the next step, the drop-off count, and the drop-off rate.
Return all steps except the final step, ordered by the largest drop-off count first.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_date	DATE	User signup date
signup_source	VARCHAR(50)	Acquisition source
country_code	VARCHAR(2)	User country

`onboarding_steps`

column	type	description
step_id	INT	Funnel step identifier
step_name	VARCHAR(50)	Funnel step name
step_order	INT	Step sequence in the funnel

`user_onboarding_events`

column	type	description
event_id	INT	Event identifier
user_id	INT	User who triggered the event
step_id	INT	Completed onboarding step
completed_at	TIMESTAMP	Completion time
device_type	VARCHAR(20)	Device used for the event

Sample Data

`users`

user_id	signup_date	signup_source	country_code
4	2024-01-20	web	GB
1	2024-01-03	web	US
7	2024-01-10	partner	US
2	2024-01-05	web	CA

`onboarding_steps`

step_id	step_name	step_order
10	Account Created	1
20	Email Verified	2
30	Profile Completed	3
40	Payment Added	4
50	First Transaction	5

`user_onboarding_events`

event_id	user_id	step_id	completed_at	device_type
1004	2	10	2024-01-05 09:00:00	mobile
1001	1	10	2024-01-03 08:00:00	desktop
1002	1	20	2024-01-03 08:10:00	desktop
1003	1	30	2024-01-03 08:25:00	desktop

Expected Output

step_name	users_at_step	users_at_next_step	drop_off_count	drop_off_rate
Profile Completed	4	1	3	0.75
Email Verified	6	4	2	0.33
Account Created	7	6	1	0.14
Payment Added	1	1	0	0.00

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
signup_source	VARCHAR(50)	Marketing or acquisition source
country_code	VARCHAR(2)	Two-letter country code

onboarding_steps

Column	Type	Description
step_idPK	INT	Unique onboarding step identifier
step_name	VARCHAR(50)	Name of the onboarding step
step_order	INT	Sequence order of the step in the funnel

user_onboarding_events

Column	Type	Description
event_idPK	INT	Unique onboarding event identifier
user_id	INT	User who completed the step
step_id	INT	Onboarding step completed
completed_at	TIMESTAMP	Timestamp when the step was completed
device_type	VARCHAR(20)	Device used for the onboarding event

Sample Data

Example 1

Input

{"users":[[4,"2024-01-20","web","GB"],[1,"2024-01-03","web","US"],[7,"2024-01-10","partner","US"],[2,"2024-01-05","web","CA"],[9,"2024-02-01","web","US"],[5,"2024-01-25","web",null],[3,"2024-01-08","web","US"],[8,"2024-01-28",null,"DE"],[6,"2023-12-30","web","FR"],[10,"2024-01-15","mobile_app","BR"],[11,"2024-01-31","web","IN"],[12,"2024-01-12","web","US"],["1","2024-01-24","comparison_site","IE"],["2","2024-02-01","sales_outreach","MX"],["3","2024-01-08","web","NL"],["4","2023-12-28","reseller"

Output

[["Account Created","7","0","7","1.00"],["Profile Completed","4","1","3","0.75"],["Email Verified","6","4","2","0.33"],["Account Created","7","6","1","0.14"],["Payment Added","1","0","1","1.00"],["First Transaction","1","0","1","1.00"],["Payment Added","1","1","0","0.00"],["Team Invited","0","0","0","0"],["Referral Sent","0","0","0","0"],["KYC Approved","0","0","0","0"],["Card Ordered","0","0","0","0"],["Debit Card Activated","0","0","0","0"],["Support Chat Opened","0","0","0","0"],["Security Qu

Problem

Task

NovaPay wants to identify the largest drop-off point in its onboarding funnel. Write a PostgreSQL query to measure how many users reached each funnel step and calculate the drop-off to the next step.

Requirements

Consider only users from the web signup source who signed up in January 2024.
Use the funnel order defined in onboarding_steps.
For each step, return the number of distinct users who completed it, the number who completed the next step, the drop-off count, and the drop-off rate.
Return all steps except the final step, ordered by the largest drop-off count first.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
signup_date	DATE	User signup date
signup_source	VARCHAR(50)	Acquisition source
country_code	VARCHAR(2)	User country

`onboarding_steps`

column	type	description
step_id	INT	Funnel step identifier
step_name	VARCHAR(50)	Funnel step name
step_order	INT	Step sequence in the funnel

`user_onboarding_events`

column	type	description
event_id	INT	Event identifier
user_id	INT	User who triggered the event
step_id	INT	Completed onboarding step
completed_at	TIMESTAMP	Completion time
device_type	VARCHAR(20)	Device used for the event

Sample Data

`users`

user_id	signup_date	signup_source	country_code
4	2024-01-20	web	GB
1	2024-01-03	web	US
7	2024-01-10	partner	US
2	2024-01-05	web	CA

`onboarding_steps`

step_id	step_name	step_order
10	Account Created	1
20	Email Verified	2
30	Profile Completed	3
40	Payment Added	4
50	First Transaction	5

`user_onboarding_events`

event_id	user_id	step_id	completed_at	device_type
1004	2	10	2024-01-05 09:00:00	mobile
1001	1	10	2024-01-03 08:00:00	desktop
1002	1	20	2024-01-03 08:10:00	desktop
1003	1	30	2024-01-03 08:25:00	desktop

Expected Output

step_name	users_at_step	users_at_next_step	drop_off_count	drop_off_rate
Profile Completed	4	1	3	0.75
Email Verified	6	4	2	0.33
Account Created	7	6	1	0.14
Payment Added	1	1	0	0.00

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
signup_source	VARCHAR(50)	Marketing or acquisition source
country_code	VARCHAR(2)	Two-letter country code

onboarding_steps

Column	Type	Description
step_idPK	INT	Unique onboarding step identifier
step_name	VARCHAR(50)	Name of the onboarding step
step_order	INT	Sequence order of the step in the funnel

user_onboarding_events

Column	Type	Description
event_idPK	INT	Unique onboarding event identifier
user_id	INT	User who completed the step
step_id	INT	Onboarding step completed
completed_at	TIMESTAMP	Timestamp when the step was completed
device_type	VARCHAR(20)	Device used for the onboarding event

Sample Data

Example 1

Input

{"users":[[4,"2024-01-20","web","GB"],[1,"2024-01-03","web","US"],[7,"2024-01-10","partner","US"],[2,"2024-01-05","web","CA"],[9,"2024-02-01","web","US"],[5,"2024-01-25","web",null],[3,"2024-01-08","web","US"],[8,"2024-01-28",null,"DE"],[6,"2023-12-30","web","FR"],[10,"2024-01-15","mobile_app","BR"],[11,"2024-01-31","web","IN"],[12,"2024-01-12","web","US"],["1","2024-01-24","comparison_site","IE"],["2","2024-02-01","sales_outreach","MX"],["3","2024-01-08","web","NL"],["4","2023-12-28","reseller"

Output

[["Account Created","7","0","7","1.00"],["Profile Completed","4","1","3","0.75"],["Email Verified","6","4","2","0.33"],["Account Created","7","6","1","0.14"],["Payment Added","1","0","1","1.00"],["First Transaction","1","0","1","1.00"],["Payment Added","1","1","0","0.00"],["Team Invited","0","0","0","0"],["Referral Sent","0","0","0","0"],["KYC Approved","0","0","0","0"],["Card Ordered","0","0","0","0"],["Debit Card Activated","0","0","0","0"],["Support Chat Opened","0","0","0","0"],["Security Qu

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 Signup Funnel Drop-OffMedium

Analyze Asana Onboarding Funnel DropoffMedium

Next question

Find Onboarding Funnel Drop-Off | Dataford Interview Questions - Dataford - Ace your Interview