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 |
