Calculate the conversion rate at each stage of the sales funnel: Lead → Opportunity → Closed Won. Show the count and percentage for each stage. The percentage should be relative to the previous stage.
This funnel analysis problem is central to Salesforce's CRM analytics. Understanding conversion rates between pipeline stages helps sales teams identify bottlenecks and optimize their processes. This question tests your ability to work with hierarchical data, calculate stage-to-stage conversion rates, and present metrics in a business-friendly format using CTEs for clarity.
Advanced concepts: Common Table Expressions (CTEs) for organizing multi-stage calculations, window functions or self-joins for comparing stages, CASE WHEN for stage categorization, and percentage calculations with proper NULL handling. The challenge is calculating 'conversion from previous stage' rather than 'conversion from start'.
Salesforce customers use similar queries to: generate pipeline health reports for sales managers, identify which stages have the lowest conversion rates, calculate sales rep performance metrics, forecast revenue based on historical conversion patterns, power executive dashboards showing funnel efficiency, and trigger automated coaching when conversion rates drop.
When tackling this Salesforce problem, the key is to understand the grain of the result. Are you returning one row per user, or one row per category? Always start by identifying your unique join keys and consider if filtered aggregations (CASE WHEN) are more efficient than multiple subqueries.
Be careful with NULL values in your JOIN conditions or aggregate functions. In interview scenarios, datasets often include edge cases like zero-count categories or duplicate entries that can throw off a simple COUNT(*) if not handled with DISTINCT.
Share your approach, optimized queries, or ask questions. Learning from others is the fastest way to master SQL.
WITH stage_counts AS (
SELECT 'Lead' AS stage, COUNT(*) AS count FROM leads
UNION ALL
SELECT 'Opportunity', COUNT(*) FROM leads WHERE status IN ('Opportunity', 'Closed Won')
UNION ALL
SELECT 'Closed Won', COUNT(*) FROM leads WHERE status = 'Closed Won'
)
SELECT
stage,
count,
ROUND(
count * 100.0
/ LAG(count, 1, count) OVER (
ORDER BY CASE stage
WHEN 'Lead' THEN 1
WHEN 'Opportunity' THEN 2
WHEN 'Closed Won' THEN 3
END
),
2
) AS conversion_rate
FROM stage_counts;The CTE materializes cumulative stage counts by re-scanning the leads table three times with progressive WHERE filters. LAG(count, 1, count) divides each stage's count by the previous stage's count, using count itself as a fallback for the first row (Lead) so it shows 100.00%. The CASE expression imposes a custom sort order since stage names are not alphabetically ordered. This pattern is explicit and easy to audit. Its downside is three sequential scans of the leads table, which is inefficient on very large datasets.
WITH stage_counts AS (
SELECT
COUNT(*) AS lead_count,
COUNT(CASE WHEN status IN ('Opportunity','Closed Won') THEN 1 END) AS opp_count,
COUNT(CASE WHEN status = 'Closed Won' THEN 1 END) AS won_count
FROM leads
)
SELECT stage, cnt,
ROUND(cnt * 100.0 / NULLIF(prev_cnt, 0), 2) AS conversion_rate
FROM (
VALUES
('Lead', lead_count, lead_count),
('Opportunity', opp_count, lead_count),
('Closed Won', won_count, opp_count)
) AS t(stage, cnt, prev_cnt)
CROSS JOIN stage_counts;This approach reads the leads table exactly once using conditional COUNT(), then pivots into rows using a VALUES clause (PostgreSQL/SQL Server) cross-joined with the CTE. Division by prev_cnt uses NULLIF to guard against zero-division. Single-pass aggregation is significantly faster on large tables. The tradeoff is that VALUES row constructors are database-specific; MySQL requires a UNION ALL subquery instead. Prefer this pattern in production ETL or dashboards where the leads table has millions of rows and minimizing I/O matters.
Solution 1 scans the leads table three times, which on a 10M-row table means 30M row reads. Each scan benefits from an index on (status) for the WHERE filters, but index range scans still accumulate. Solution 2 performs a single full scan and computes all three aggregates simultaneously, halving or more the I/O cost. Both solutions produce tiny result sets (3 rows), so the LAG window computation and final sort are negligible. The dominant cost is always the source scan. Partitioning the leads table by status or maintaining a pre-aggregated materialized view of stage counts makes both solutions instant for reporting. For real-time dashboards at Salesforce scale, incremental aggregation with triggers or streaming pipelines is preferred over ad-hoc full-table queries.
A frequent error is treating stage counts as mutually exclusive — counting Opportunity rows separately from Closed Won rows produces a funnel where Closed Won is double-counted or excluded from Opportunity. The correct model is cumulative: a Closed Won lead has passed through Opportunity, so Opportunity count includes Closed Won. Another pitfall is using WHERE stage ordering: LAG without an explicit ORDER BY in the OVER clause can return rows in non-deterministic order, producing garbage conversion rates. Candidates often forget the fallback parameter in LAG, causing NULL for the first stage. Dividing by zero when a stage has no leads (NULLIF guard) is also frequently missed.
When LAG finds no previous row (first row in the window), it returns the fallback. Setting it to count makes the division count/count = 1.0, yielding 100.00% conversion for the Lead stage, which is correct — all leads start at the top of the funnel. Using 1 would give count * 100.0 / 1, inflating the rate to the raw lead count as a percentage.
Add a fourth row to the UNION ALL in the CTE with the appropriate WHERE clause for the Churned status, and extend the CASE expression in the OVER clause to assign it an order value of 4. LAG will automatically reference the Closed Won count as its previous stage. No other changes are needed.
Stage-over-stage (LAG) shows the drop-off between adjacent stages, e.g., Opportunity/Lead. Top-of-funnel shows each stage as a fraction of total leads: Closed Won/Lead. Stage-over-stage reveals where the biggest leakage occurs. Top-of-funnel gives an absolute success rate. Interviewers may ask you to compute both simultaneously by including a second division expression.