




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.
completed_flag = true.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 |
Representative rows are provided below in the structured sample data section.
| 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 |
| Column | Type | Description |
|---|---|---|
| step_idPK | INT | Unique step identifier |
| funnel_version | VARCHAR(20) | Funnel version label |
| step_name | VARCHAR(50) | Name of the funnel step |
| step_order | INT | Step position within the funnel |
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique user identifier |
| acquisition_channel | VARCHAR(30) | Marketing acquisition source |
| signup_date | DATE | Date the user signed up |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique event identifier |
| user_id | INT | User who triggered the event |
| step_id | INT | Funnel step reached |
| event_time | TIMESTAMP | Time of the event |
| completed_flag | BOOLEAN | Whether the step was completed |
{"users":[["101","Organic Search","2024-04-01"],["102","Paid Social","2024-04-01"],["103","Referral","2024-04-02"],["104","Email","2024-04-02"],["105","Organic Search","2024-04-03"],["106",null,"2024-04-03"],["107","Affiliate","2024-04-04"],["108","Paid Search","2024-04-04"],["109","","2024-04-05"],["110","Podcast","2024-04-05"],["111","null","2024-04-01"],["112","Push Notification","2024-04-06"],["113","Paid Search","2024-04-03"],["114","Instagram","2024-04-01"],["115","Affiliate","2024-04-08"]Output[["experiment_13","address_entry","download_app","0","0","0","null"],["fall_test","complete_profile","first_purchase","0","0","0","null"],["mobile_v1","choose_plan","create_account","0","0","0","null"],["v1","verify_email","landing_page","3","0","3","1.0000"],["v2","create_account","choose_plan","3","1","2","0.6667"]]