Onboarding Drop-Off Query | Dataford Interview Questions

Given a large database of user interactions, write a query to identify drop-off points in the client onboarding journey at ADP.

Use onboarding_steps and user_interactions to measure progression between consecutive onboarding steps.

Output

One row per onboarding step that has a subsequent step.
Columns: step_order, step_name, users_reached, users_progressed, users_dropped, and dropoff_rate_pct.
Include users who reached a step but did not reach the next step. Order by step_order ascending.

onboarding_steps

Column	Type	Description
step_idPK	INT	Unique onboarding step identifier
step_name	VARCHAR(100)	Name of the onboarding step
step_order	INT	Position of the step in the onboarding journey

user_interactions

Column	Type	Description
interaction_idPK	INT	Unique interaction identifier
user_id	INT	Client user identifier
step_id	INT	Onboarding step associated with the interaction
interaction_type	VARCHAR(50)	Type of interaction recorded
occurred_at	TIMESTAMP	Timestamp when the interaction occurred

Your question is Onboarding Drop-Off Query. Start with the requirements and the two 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.