

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.
web signup source who signed up in January 2024.onboarding_steps.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 |
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 |
| 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 |
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique user identifier |
| signup_date | DATE | Date the user signed up |
| signup_source | VARCHAR(50) | Marketing or acquisition source |
| country_code | VARCHAR(2) | Two-letter country code |
| Column | Type | Description |
|---|---|---|
| step_idPK | INT | Unique onboarding step identifier |
| step_name | VARCHAR(50) | Name of the onboarding step |
| step_order | INT | Sequence order of the step in the funnel |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique onboarding event identifier |
| user_id | INT | User who completed the step |
| step_id | INT | Onboarding step completed |
| completed_at | TIMESTAMP | Timestamp when the step was completed |
| device_type | VARCHAR(20) | Device used for the onboarding event |
{"users":[[4,"2024-01-20","web","GB"],[1,"2024-01-03","web","US"],[7,"2024-01-10","partner","US"],[2,"2024-01-05","web","CA"],[9,"2024-02-01","web","US"],[5,"2024-01-25","web",null],[3,"2024-01-08","web","US"],[8,"2024-01-28",null,"DE"],[6,"2023-12-30","web","FR"],[10,"2024-01-15","mobile_app","BR"],[11,"2024-01-31","web","IN"],[12,"2024-01-12","web","US"],["1","2024-01-24","comparison_site","IE"],["2","2024-02-01","sales_outreach","MX"],["3","2024-01-08","web","NL"],["4","2023-12-28","reseller"Output[["Account Created","7","0","7","1.00"],["Profile Completed","4","1","3","0.75"],["Email Verified","6","4","2","0.33"],["Account Created","7","6","1","0.14"],["Payment Added","1","0","1","1.00"],["First Transaction","1","0","1","1.00"],["Payment Added","1","1","0","0.00"],["Team Invited","0","0","0","0"],["Referral Sent","0","0","0","0"],["KYC Approved","0","0","0","0"],["Card Ordered","0","0","0","0"],["Debit Card Activated","0","0","0","0"],["Support Chat Opened","0","0","0","0"],["Security Qu