Find Onboarding Funnel Drop-Off | Dataford Interview Questions

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.

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

Your question is Find Onboarding 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