Build a SQL query for a multi-step conversion funnel
Tests your ability to translate a product question into robust SQL. A great answer uses CTEs or left joins to count users at each step, defining the attribution model (e.g., first-touch) and time windows. A red flag is a naive query that double-counts users.
What's really being asked
This question tests your ability to translate an ambiguous product requirement into a precise, correct, and robust analytical query. It's not just about SQL syntax; it's about your ability to model a user journey in data. The interviewer is looking for you to proactively define the parameters of the funnel (e.g., sequence, time windows, attribution) and structure a query that correctly represents the flow of users, avoiding common pitfalls like double-counting.
The full answer
A strong answer is a structured plan, not just a block of code. It hits three key points in order. First, clarify the business logic: what are the specific event names for each step? Must they be completed in sequence? What is the time window for completion (e.g., within 7 days of the first step)? Second, propose a SQL strategy, typically using Common Table Expressions (CTEs). A great approach is to use one CTE to find the first timestamp for each user for each relevant event (first-touch attribution), then use a series of LEFT JOINs or conditional aggregations (COUNT(CASE WHEN event_name = 'step_1' THEN user_id END)) to build the funnel counts. Third, explain how to calculate drop-off: for each step, calculate (users_in_previous_step - users_in_current_step) / users_in_previous_step to find the percentage drop-off. The largest percentage is the biggest friction point.
The mistakes people make
The most common red flag is writing a naive query like SELECT event_name, COUNT(DISTINCT user_id) FROM events GROUP BY event_name. This is fundamentally wrong because it doesn't enforce sequence. A user who completed step 3 but skipped step 2 would be counted in both, making the funnel nonsensical. Another mistake is ignoring time constraints. A user completing step 2 a year after step 1 is not part of a meaningful onboarding funnel. Finally, failing to specify an attribution model (like first-touch) shows a lack of depth. For an onboarding funnel, you almost always want to measure the first time a user successfully passes a milestone, not subsequent attempts.
What usually comes next
Expect questions like: "How would you cohort this analysis by sign-up week to see if onboarding is improving over time?" (Answer: Add a join to the users table and group by DATE_TRUNC('week', signup_date)). Or, "This query is slow; how would you optimize it?" (Answer: Ensure indexes on (user_id, timestamp) in the events table, filter to the smallest possible time window early, or use pre-aggregated data tables). Another common one is "How would you measure the average time between steps?" (Answer: Use the timestamps from your CTEs and calculate AVG(step2_timestamp - step1_timestamp)).
A concrete example
Imagine our funnel has three steps: 'signup', 'create_profile', and 'send_first_message'. Our query returns: 10,000 users for 'signup', 7,000 for 'create_profile', and 6,300 for 'send_first_message'. The drop-off from signup to profile creation is (10000 - 7000) / 10000 = 30%. The drop-off from profile creation to sending a message is (7000 - 6300) / 7000 = 10%. The biggest drop-off point is clearly the 30% loss after initial signup, so we should investigate why users aren't completing their profiles.
Interview question
An analyst builds a funnel query using `GROUP BY event_name` to count distinct users for each event. Why is this approach fundamentally flawed for a sequential funnel?
- a.It cannot be used to apply a time window between the first and last step.
- b.It is less performant than using Common Table Expressions (CTEs).
- c.It fails to de-duplicate users who might complete the same step multiple times.
- d.It incorrectly counts users in a later step even if they skipped a required earlier step.Correct
Why? this is the answer
This approach is flawed because it treats each event independently, violating the sequential nature of a funnel. The correct answer is right because a user who completes step 3 but not step 2 will be counted in step 3, making the funnel counts nonsensical. A tempting distractor is the de-duplication issue, but this is typically solved by using `COUNT(DISTINCT user_id)` and is not the primary logical error.
Just read this? Test yourself on what you have been reading.
Read the original → quadratichq.com
- #sql
- #analytics
- #product metrics
- #data modeling
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