How would you build a SQL query for a conversion funnel?
Tests your ability to translate a business need into a technical SQL solution. A good answer uses CTEs or LEFT JOINs to model sequential steps, counts users at each stage, and discusses attribution.
What's really being asked
This question isn't just about SQL syntax; it tests your product and data sense. The interviewer wants to see if you think like a product analyst before you write code. They are evaluating your ability to ask clarifying questions about the business logic—step definitions, time windows, attribution—before building the technical solution. A senior candidate connects the code to the business outcome, ensuring the analysis is meaningful and not just technically correct.
The full answer
A strong answer hits four points in order. First, ask clarifying questions to define the funnel: What are the exact steps? Must they be sequential? What's the time window for completion (e.g., within 24 hours)? What attribution model should be used (e.g., first-touch)? Second, outline the SQL strategy. A common and readable pattern uses Common Table Expressions (CTEs) for each step. The first CTE selects users who started the funnel (e.g., event = 'signup'). Subsequent CTEs use a LEFT JOIN on the previous step's user list to find those who also completed the next step, often with a timestamp condition. Third, describe the aggregation. The final SELECT statement will COUNT(DISTINCT user_id) from each CTE to get the user count per step. Fourth, explain how to find the drop-off. Calculate the percentage change between consecutive steps using the counts from the previous stage. The largest percentage decrease is the biggest friction point.
The mistakes people make
The biggest red flag is jumping straight into writing a complex query without asking any clarifying questions. This suggests you might build the wrong thing. Another common mistake is proposing a series of independent COUNT queries for each event. This is incorrect because it doesn't enforce the sequential nature of a funnel; it just counts how many users ever did each action, not how many progressed from Step 1 to Step 2. A junior answer might also forget to use COUNT(DISTINCT user_id), leading to inflated counts if a user can trigger an event multiple times.
What usually comes next
Expect questions like: "How would you change this query to analyze conversion by user cohort, like users who signed up in January vs. February?" (Answer: Add a GROUP BY on the signup date/cohort identifier). "What if the steps don't need to be sequential?" (Answer: Use conditional aggregation with COUNT(DISTINCT CASE WHEN...) instead of sequential joins). "How would this scale if you have billions of events?" (Answer: Discuss pre-aggregation, materialized views, or using specialized analytics databases).
A concrete example
For a 3-step funnel (Signup -> Verify Email -> Create Project), imagine 10,000 users sign up (Step 1). If 9,000 verify their email (Step 2), that's a 10% drop-off. If 8,500 of those then create a project (Step 3), the drop-off from Step 2 to 3 is (9000 - 8500) / 9000 = 5.6%. The biggest drop-off point is clearly between Signup and Verify Email, indicating potential friction with email delivery, spam filters, or user motivation.
Interview question
Which SQL strategy is most effective for building a conversion funnel that accurately tracks sequential user progression and identifies drop-off points?
- a.Employing Common Table Expressions (CTEs) for each step, linked by LEFT JOINs on user ID and ordered by timestamp.Correct
- b.Creating temporary tables for each step and then using INNER JOINs to filter users who completed all steps.
- c.Using a single query with conditional aggregation (e.g., COUNT(DISTINCT CASE WHEN ...)) for all steps.
- d.Executing independent COUNT queries for each event and then manually comparing results.
Why? this is the answer
The card recommends using CTEs for each step and LEFT JOINs to connect them sequentially, often with a timestamp condition, as this pattern is common, readable, and allows for tracking progression and calculating drop-offs. Option D is a common wrong answer because it fails to enforce the sequential nature of a funnel.
Just read this? Test yourself on what you have been reading.
Read the original → quadratichq.com
You just looked this up. Could you explain it out loud?
That is the part interviews actually test. Tezvyn takes questions like this one and gives you what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Open roles that interview on sql — each one lists the topics its interview covers.
See open roles