Top Lead Sources Query | Dataford Interview Questions

Cvent wants to compare lead-source performance across the last three completed calendar quarters. Write a PostgreSQL query that evaluates every lead source, including sources with no leads in the reporting period.

For each source, calculate total leads, qualified leads, and the percentage of leads whose status is qualified. Rank sources by qualified-lead count, qualification rate, total-lead count, and source name as deterministic tie-breakers.

Output

Return at most three rows, with one row per ranked lead source.
Include source_rank, source_name, total_leads, qualified_leads, and qualification_rate.
Use the three completed quarters ending before the fixed reporting boundary of July 1, 2026.
Include sources with no matching leads, treating their counts as zero and their qualification rate as NULL.
Sort by source_rank ascending. NULL rates must sort after non-NULL rates when ranking.

Schema

lead_sources

Column	Type	Description
source_idPK	INT	Unique identifier for a Cvent lead source
source_name	VARCHAR(100)	Display name of the lead source

leads

Column	Type	Description
lead_idPK	INT	Unique identifier for a lead
source_id	INT	Lead source identifier
created_at	DATE	Date the lead was created
lead_status	VARCHAR(30)	Current lead status

Your question is Top Lead Sources Query. Start with the requirements and the two 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