Task
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.
Requirements
- Count only users whose
signup_date falls within the last 30 days relative to DATE '2024-06-30'.
- Attribute each signup to the referrer in
referral_events using referred_user_id.
- Exclude rows where the referrer name is
NULL.
- Return the top 3 referrers ordered by signup count descending, then referrer name ascending for tie-breaking.
- Output columns:
referrer_name, signup_count.
Table Definitions
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 |
Sample Data
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 | Instagram |
| 2 | 102 | Facebook Profile Link | Facebook |
| 3 | 103 | Creator Campaign A | Instagram |
| 4 | 104 | WhatsApp Share | Instagram |
| 5 | 105 | Reels Creator Promo | Instagram |
| 6 | 107 | WhatsApp Share | Instagram |
| 7 | 108 | Facebook Profile Link | Facebook |
| 8 | 109 | Reels Creator Promo | Instagram |
| 9 | 110 | Creator Campaign A | Instagram |
| 10 | 111 | Messenger Invite | Messenger |
| 11 | 105 | Reels Creator Promo | Instagram |
| 12 | 102 | | Instagram |
Expected Output
| referrer_name | signup_count |
|---|
| Reels Creator Promo | 2 |
| WhatsApp Share | 2 |
| Creator Campaign A | 1 |
