Lead-to-Opportunity Conversion SQL | Dataford Interview Questions

Write a SQL query to calculate lead-to-opportunity conversion rate by marketing channel over time at Pluralsight.

Use the provided channels, leads, and opportunities data. Treat a lead as converted when it has at least one opportunity created on or after the lead creation date.

Output

One row per marketing channel and lead-created month.
Columns: channel_name, month, lead_count, opportunity_count, and conversion_rate_pct.
Include only months with leads, order by month ascending and channel name ascending, and calculate the rate as a percentage rounded to two decimal places.

Schema

channels

Column	Type	Description
channel_idPK	INT	Unique marketing channel identifier
channel_name	VARCHAR(100)	Marketing channel name

leads

Column	Type	Description
lead_idPK	INT	Unique lead identifier
channel_id	INT	Marketing channel associated with the lead
created_at	TIMESTAMP	Timestamp when the lead was created

opportunities

Column	Type	Description
opportunity_idPK	INT	Unique opportunity identifier
lead_id	INT	Lead associated with the opportunity
created_at	TIMESTAMP	Timestamp when the opportunity was created

Your question is Lead-to-Opportunity Conversion SQL. 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