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.