Sales Funnel SQL Analysis | Dataford Interview Questions

Write a SQL query to analyze Synthesia sales funnel performance by stage, including conversion rates and drop-off between stages.

Use the provided funnel stage definitions and stage history records. Calculate metrics across all defined stages, including stages with no recorded opportunities.

Output

One row per funnel stage, ordered by stage_order ascending.
Columns: stage_name, stage_count, conversion_rate, and dropoff_rate.
The first stage has a 100% conversion rate and 0% drop-off. Return NULL when a rate cannot be calculated because the preceding stage has zero opportunities.

Schema

funnel_stages

Column	Type	Description
stage_idPK	INT	Unique funnel stage identifier
stage_name	VARCHAR(50)	Name of the Synthesia sales funnel stage
stage_order	INT	Sequential position of the stage in the funnel

opportunity_stage_history

Column	Type	Description
history_idPK	INT	Unique stage history record identifier
opportunity_id	INT	Identifier of the sales opportunity
stage_id	INT	Funnel stage reached by the opportunity
entered_at	DATE	Date when the opportunity entered the stage

Your question is Sales Funnel SQL Analysis. 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