Task
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 |
Sample Data
users
| user_id | signup_date | signup_source | country_code |
|---|
| 4 | 2024-01-20 | web | GB |
| 1 | 2024-01-03 | web | US |
| 7 | 2024-01-10 | partner | US |
| 2 | 2024-01-05 | web | CA |
onboarding_steps
| step_id | step_name | step_order |
|---|
| 10 | Account Created | 1 |
| 20 | Email Verified | 2 |
| 30 | Profile Completed | 3 |
| 40 | Payment Added | 4 |
| 50 | First Transaction | 5 |
user_onboarding_events
| event_id | user_id | step_id | completed_at | device_type |
|---|
| 1004 | 2 | 10 | 2024-01-05 09:00:00 | mobile |
| 1001 | 1 | 10 | 2024-01-03 08:00:00 | desktop |
| 1002 | 1 | 20 | 2024-01-03 08:10:00 | desktop |
| 1003 | 1 | 30 | 2024-01-03 08:25:00 | desktop |
Expected Output
| step_name | users_at_step | users_at_next_step | drop_off_count | drop_off_rate |
|---|
| Profile Completed | 4 | 1 | 3 | 0.75 |
| Email Verified | 6 | 4 | 2 | 0.33 |
| Account Created | 7 | 6 | 1 | 0.14 |
| Payment Added | 1 | 1 | 0 | 0.00 |
