Task
At Meta, the growth team tracks the Activation step of the AARRR funnel for new Instagram users. Write a SQL query to calculate the percentage of users who completed onboarding within 24 hours of signup.
Requirements
- Use
ig_signups as the denominator: count all users who signed up.
- A user counts as onboarded if they have at least one
onboarding_completed event in ig_onboarding_events that occurs on or after signup and within 24 hours of signup_ts.
- Return:
- total signed up users
- users who completed onboarding within 24 hours
- onboarding completion percentage rounded to 2 decimals
- Handle users with no onboarding event and users with multiple onboarding events correctly.
Table Definitions
ig_signups
| column | type | description |
|---|
| user_id | INT | Instagram user identifier |
| signup_ts | TIMESTAMP | Time the user signed up |
| signup_surface | VARCHAR(50) | Entry surface such as Reels or FB Groups |
ig_onboarding_events
| column | type | description |
|---|
| event_id | INT | Event identifier |
| user_id | INT | Instagram user identifier |
| event_name | VARCHAR(50) | Onboarding event name |
| event_ts | TIMESTAMP | Time the event occurred |
Sample Data
ig_signups
| user_id | signup_ts | signup_surface |
|---|
| 104 | 2024-06-03 20:00:00 | reels |
| 101 | 2024-06-01 09:00:00 | reels |
| 108 | 2024-06-05 23:30:00 | reels |
| 103 | 2024-06-02 15:45:00 | fb_groups |
ig_onboarding_events
| event_id | user_id | event_name | event_ts |
|---|
| 3 | 102 | onboarding_completed | 2024-06-03 10:00:00 |
| 7 | 105 | onboarding_completed | 2024-06-04 08:30:00 |
| 10 | 108 | onboarding_step_viewed | 2024-06-06 00:10:00 |
| 12 | 110 | onboarding_completed | 2024-06-06 10:00:00 |
Expected Output
| total_signed_up_users | onboarded_within_24h_users | onboarding_completion_pct |
|---|
| 10 | 5 | 50.00 |
