NovaPay wants to identify the largest drop-off point in its onboarding funnel. Write a PostgreSQL query to measure how many users reached each funnel step and calculate the drop-off to the next step.
Requirements
- Consider only users from the
web signup source who signed up in January 2024.
- Use the funnel order defined in
onboarding_steps.
- For each step, return the number of distinct users who completed it, the number who completed the next step, the drop-off count, and the drop-off rate.
- Return all steps except the final step, ordered by the largest drop-off count first.
Table Definitions
users
| column | type | description |
|---|
| user_id | INT | Unique user identifier |
| signup_date | DATE | User signup date |
| signup_source | VARCHAR(50) | Acquisition source |
| country_code | VARCHAR(2) | User country |
onboarding_steps
| column | type | description |
|---|
| step_id | INT | Funnel step identifier |
| step_name | VARCHAR(50) | Funnel step name |
| step_order | INT | Step sequence in the funnel |
user_onboarding_events
| column | type | description |
|---|
| event_id | INT | Event identifier |
| user_id | INT | User who triggered the event |
| step_id | INT | Completed onboarding step |
| completed_at | TIMESTAMP | Completion time |
| device_type | VARCHAR(20) | Device used for the event |
