Task
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
Table Definitions
users
| column | type | description |
|---|
| user_id | INT | Unique user identifier |
| signup_date | DATE | Date the user registered |
| acquisition_channel | VARCHAR(50) | Marketing source |
funnel_steps
| column | type | description |
|---|
| step_name | VARCHAR(50) | Funnel step name |
| step_order | INT | Step sequence |
events
| column | type | description |
|---|
| event_id | INT | Unique event identifier |
| user_id | INT | User who generated the event |
| event_name | VARCHAR(50) | Event type |
| event_time | TIMESTAMP | Event timestamp |
| device_type | VARCHAR(20) | Device used |
Sample Data
users
| user_id | signup_date | acquisition_channel |
|---|
| 4 | 2024-01-20 | Paid Search |
| 1 | 2024-01-03 | Organic Search |
| 7 | 2024-02-01 | Referral |
funnel_steps
| step_name | step_order |
|---|
| landing_view | 1 |
| signup_started | 2 |
| email_verified | 3 |
| subscription_started | 4 |
events
| event_id | user_id | event_name | event_time | device_type |
|---|
| 103 | 1 | landing_view | 2024-01-03 09:00:00 | web |
| 110 | 2 | signup_started | 2024-01-06 10:00:00 | mobile |
| 118 | 4 | subscription_started | 2024-01-29 11:00:00 | web |
Expected Output
| step_name | step_order | users_reached | users_dropped_before_next_step | drop_off_pct |
|---|
| landing_view | 1 | 5 | 1 | 20.00 |
| signup_started | 2 | 4 | 1 | 25.00 |
| email_verified | 3 | 3 | 1 | 33.33 |
| subscription_started | 4 | 2 | 2 | 100.00 |
