Task
StreamFlow tracks users moving through a signup funnel. Write a SQL query to identify the biggest drop-off between consecutive funnel steps for each funnel version.
Requirements
- Consider only events where
completed_flag = true.
- Count distinct users who completed each step within each funnel version.
- Compare each step to the immediately previous step in the same funnel version.
- Return the step with the largest user drop-off per funnel version, including the previous step name, current step name, previous step users, current step users, drop_off_users, and drop_off_rate.
Table Definitions
funnel_steps
| column | type | description |
|---|
| step_id | INT | Unique step identifier |
| funnel_version | VARCHAR(20) | Funnel version |
| step_name | VARCHAR(50) | Step label |
| step_order | INT | Step sequence in the funnel |
users
| column | type | description |
|---|
| user_id | INT | Unique user identifier |
| acquisition_channel | VARCHAR(30) | Marketing source |
| signup_date | DATE | User signup date |
funnel_events
| column | type | description |
|---|
| event_id | INT | Unique event identifier |
| user_id | INT | User who triggered the event |
| step_id | INT | Funnel step reached |
| event_time | TIMESTAMP | Event timestamp |
| completed_flag | BOOLEAN | Whether the step was completed |
Sample Data
Representative rows are provided below in the structured sample data section.
Expected Output
| funnel_version | previous_step | current_step | previous_step_users | current_step_users | drop_off_users | drop_off_rate |
|---|
| v1 | verify_email | add_payment | 3 | 1 | 2 | 0.6667 |
| v2 | create_account | choose_plan | 3 | 1 | 2 | 0.6667 |
