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.
ig_signups as the denominator: count all users who signed up.onboarding_completed event in ig_onboarding_events that occurs on or after signup and within 24 hours of signup_ts.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 |
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 |
| total_signed_up_users | onboarded_within_24h_users | onboarding_completion_pct |
|---|---|---|
| 10 | 5 | 50.00 |
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Instagram user identifier |
| signup_ts | TIMESTAMP | Timestamp when the user signed up |
| signup_surface | VARCHAR(50) | Signup entry surface in Meta products |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique event identifier |
| user_id | INT | Instagram user identifier tied to the event |
| event_name | VARCHAR(50) | Onboarding event type |
| event_ts | TIMESTAMP | Timestamp when the event occurred |
{"ig_signups":[[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"],[110,"2024-06-06 09:00:00",null],[106,"2024-06-04 11:00:00","ig_save"],[102,"2024-06-01 10:30:00","fb_groups"],[109,"2024-06-06 08:00:00",""],[105,"2024-06-03 08:00:00","reels"],[107,"2024-06-05 00:00:00","fb_groups"],["1","2024-06-08 05:52:42","ios_app"],["2","2024-06-02 20:08:36","reels_tab"],["3","2024-05-30 03:50:16","search"],["4"Output[["30","4","13.33"]]
