SQL for Top Converting Sources | Dataford Interview Questions

Write a query to find the top 3 converting lead sources for each region over the last 30 days. Use created_at to define the period and converted = TRUE to identify conversions.

Output

One row per qualifying region and lead source
Columns: region_name, source_name, total_leads, converted_leads, conversion_rate, and source_rank
Include at most three sources per region, ordered by region and rank, with conversion rate descending and source name as the tie-breaker

Schema

regions

Column	Type	Description
region_idPK	INT	Unique region identifier
region_name	VARCHAR(100)	Region name

lead_sources

Column	Type	Description
source_idPK	INT	Unique lead source identifier
source_name	VARCHAR(100)	Marketing or referral source name

leads

Column	Type	Description
lead_idPK	INT	Unique lead identifier
region_id	INT	Associated region identifier
source_id	INT	Associated lead source identifier
created_at	DATE	Date the lead was created
converted	BOOLEAN	Whether the lead converted

Your question is SQL for Top Converting Sources. Start with the requirements and the three tables in the Question tab.

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

Problem