Lead-to-Opportunity Bottleneck SQL | Dataford Interview Questions

Write a SQL query to identify the bottleneck in a lead-to-opportunity funnel at Thales. Compare consecutive funnel stages and return the stage with the lowest conversion rate. Consider only valid leads and distinct stage progression.

Output

One row for the bottleneck stage.
Columns: stage_name, stage_number, entered_count, advanced_count, dropoff_count, conversion_rate, and bottleneck_rank.
Order by lowest conversion rate, then lowest stage number.

Schema

leads

Column	Type	Description
lead_idPK	INT	Unique lead identifier
source	VARCHAR(100)	Original marketing source
created_at	DATE	Date the lead entered the system

funnel_stages

Column	Type	Description
stage_idPK	INT	Unique funnel stage identifier
stage_name	VARCHAR(100)	Funnel stage label
stage_number	INT	Stage order in the funnel

lead_stage_events

Column	Type	Description
event_idPK	INT	Unique stage event identifier
lead_id	INT	Lead associated with the event
stage_id	INT	Stage reached by the lead
event_date	DATE	Date of the stage event

opportunities

Column	Type	Description
opportunity_idPK	INT	Unique opportunity identifier
lead_id	INT	Lead that generated the opportunity
opportunity_name	VARCHAR(150)	Opportunity label
estimated_value	DECIMAL(12,2)	Estimated opportunity value

Your question is Lead-to-Opportunity Bottleneck SQL. Start with the requirements and the four 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