Top Referrers Driving Recent Signups | Dataford Interview Questions

Your question is Top Referrers Driving Recent Signups. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

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.

Schema

users

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

referral_events

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