Analyze Signup Funnel Drop-Off | Dataford Interview Questions

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

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

Your question is Analyze Signup 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