Instagram Growth wants to identify which referrers are driving the most new account signups in the last 30 days. Write a SQL query to return the top 3 referrers by number of signups.
signup_date falls within the last 30 days relative to DATE '2024-06-30'.referral_events using referred_user_id.NULL.referrer_name, signup_count.users| column | type | description |
|---|---|---|
| user_id | INT | Unique user identifier |
| full_name | VARCHAR(100) | User's display name |
| signup_date | DATE | Date the user created an account |
referral_events| column | type | description |
|---|---|---|
| referral_id | INT | Unique referral event identifier |
| referred_user_id | INT | User who signed up from a referral |
| referrer_name | VARCHAR(100) | Referrer source or partner name |
| referrer_surface | VARCHAR(50) | Meta surface where referral originated |
users| user_id | full_name | signup_date |
|---|---|---|
| 101 | Ava Chen | 2024-06-29 |
| 102 | Ben Ortiz | 2024-06-15 |
| 103 | Chloe Kim | 2024-05-31 |
| 104 | Diego Patel | 2024-06-05 |
| 105 | Emma Ross | 2024-06-30 |
| 106 | Farah Ali | 2024-05-20 |
| 107 | Gabe Liu | 2024-06-10 |
| 108 | Hana Park | 2024-06-01 |
| 109 | Ian Brooks | 2024-06-18 |
| 110 | Julia Singh | 2024-06-27 |
referral_events| referral_id | referred_user_id | referrer_name | referrer_surface |
|---|---|---|---|
| 1 | 101 | WhatsApp Share | |
| 2 | 102 | Facebook Profile Link | |
| 3 | 103 | Creator Campaign A | |
| 4 | 104 | WhatsApp Share | |
| 5 | 105 | Reels Creator Promo | |
| 6 | 107 | WhatsApp Share | |
| 7 | 108 | Facebook Profile Link | |
| 8 | 109 | Reels Creator Promo | |
| 9 | 110 | Creator Campaign A | |
| 10 | 111 | Messenger Invite | Messenger |
| 11 | 105 | Reels Creator Promo | |
| 12 | 102 |
| referrer_name | signup_count |
|---|---|
| Reels Creator Promo | 2 |
| WhatsApp Share | 2 |
| Creator Campaign A | 1 |
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique user identifier |
| full_name | VARCHAR(100) | User's display name |
| signup_date | DATE | Date the user signed up |
| Column | Type | Description |
|---|---|---|
| referral_idPK | INT | Unique referral event identifier |
| referred_user_id | INT | User who signed up from a referral |
| referrer_name | VARCHAR(100) | Name of the referrer source or partner |
| referrer_surface | VARCHAR(50) | Meta surface where the referral originated |
| user_id | full_name | signup_date |
|---|---|---|
| 101 | Ava Chen | 2024-06-29 |
| 102 | Ben Ortiz | 2024-06-15 |
| 103 | Chloe Kim | 2024-05-31 |
| 104 | Diego Patel | 2024-06-05 |
| 105 | Emma Ross | 2024-06-30 |
| 106 | Farah Ali | 2024-05-20 |
| 107 | Gabe Liu | 2024-06-10 |
| 108 | Hana Park | 2024-06-01 |
| 109 | Ian Brooks | 2024-06-18 |
| 110 | Julia Singh | 2024-06-27 |
| 111 | Kai Morgan | |
| 1 | Samir Khan | 2024-06-08 |
| 2 | Gabe Liu | 2024-06-24 |
| 3 | Zane Cooper | 2024-05-31 |
| 4 | Julia Singh | 2024-06-12 |
| 5 | Mason Reed | 2024-06-24 |
| 6 | Aria Bennett | 2024-06-28 |
| 7 | Aria Bennett | 2024-05-29 |
| 8 | Ximena Flores | 2024-06-23 |
| 9 | Samir Khan | 2024-05-18 |
| 10 | Emma Ross | 2024-05-27 |
| 11 | Caleb Ward | 2024-05-26 |
| 12 | Ava Chen | 2024-07-02 |
| 13 | Aria Bennett | 2024-05-31 |
| 14 | Farah Ali | null |
| 15 | Zane Cooper | 2024-06-20 |
| 16 | Yara Haddad | null |
| 17 | Emma Ross | 2024-06-10 |
| referral_id | referred_user_id | referrer_name | referrer_surface |
|---|---|---|---|
| 1 | 101 | WhatsApp Share | |
| 2 | 102 | Facebook Profile Link | |
| 3 | 103 | Creator Campaign A | |
| 4 | 104 | WhatsApp Share | |
| 5 | 105 | Reels Creator Promo | |
| 6 | 107 | WhatsApp Share | |
| 7 | 108 | Facebook Profile Link | |
| 8 | 109 | Reels Creator Promo | |
| 9 | 110 | Creator Campaign A | |
| 10 | 111 | Messenger Invite | Messenger |
| 11 | 105 | Reels Creator Promo | |
| 12 | 102 | ||
| 1 | 3 | UGC Campaign | Search |
| 2 | 83 | Facebook Profile Link | Facebook Feed |
| 3 | 63 | Creator Campaign B | Facebook Feed |
| 4 | 84 | WhatsApp Share | Search |
| 5 | 51 | Creator Campaign B | WhatsApp Channel |
| 6 | 34 | Friend Invite | Direct Share |
| 7 | 36 | Referral Code Summer | null |
| 8 | 32 | Event Signup Link | Threads Feed |
| 9 | 33 | Influencer Bundle | Marketplace |
| 10 | 95 | Partnership Landing Page | Messenger |
| 11 | 27 | Cross-App Promo | Marketplace |
| 12 | 89 | App Store Feature | Creator Marketplace |
| 13 | 93 | Cross-App Promo | Ads Manager |
| 14 | 23 | null | Business Page |
| 15 | 68 | null | |
| 16 | 27 | Affiliate Link | Creator Marketplace |
| 17 | 72 | Facebook Profile Link | Messenger Inbox |
| referrer_name | signup_count |
|---|---|
| WhatsApp Share | 3 |
| Facebook Profile Link | 2 |
| Reels Creator Promo | 2 |
