Calculate a 3-step user onboarding funnel with SQL
Tests your ability to translate a business need into a robust data query. A great answer clarifies funnel logic (attribution, timing), defines the user cohort, finds each user's first event for each step, and then calculates conversion.
What's really being asked
Your ability to model user behavior with data. It's not a pure SQL test; it's about translating a business concept (a funnel) into a precise, robust technical implementation. Interviewers are looking for your ability to identify and handle the ambiguities inherent in event data, like out-of-order events, repeat actions, and time-to-convert windows.
The full answer
Four key points in order. First, clarify the funnel's business logic: Is it strict sequential order? What's the attribution model (first-touch is standard for this)? What's the maximum time allowed between steps? Second, outline the SQL strategy: create a CTE to find the cohort of users who signed up in the last 30 days. Third, create another CTE that joins the event stream to the cohort, using conditional aggregation (MIN(CASE WHEN...)) to pivot event timestamps into columns for each user (ts_step1, ts_step2, ts_step3). Fourth, write the final query that counts users who completed each step (e.g., COUNT(ts_step1), COUNT(CASE WHEN ts_step2 > ts_step1 THEN user_id END), etc.) and calculates the conversion rates.
The mistakes people make
The most common mistake is writing a simple query without asking clarifying questions. This shows a lack of senior-level thinking. Another red flag is a query that doesn't enforce the sequence of events (i.e., timestamp_step2 > timestamp_step1). This leads to incorrect counts. Also, simply counting distinct users for each event type independently (SELECT event_name, COUNT(DISTINCT user_id) ... GROUP BY 1) is fundamentally wrong as it doesn't track a single user's journey. Finally, failing to handle duplicate events by picking the first one (e.g., using MIN(timestamp)) will overcount or misattribute conversions.
What usually comes next
How would you change this to measure time-to-convert between steps? (Calculate AVG(timestamp_diff)). How would you scale this system if you had billions of events per day? (Move from a query on the data warehouse to a pre-aggregated pipeline using Spark/Flink). How would you visualize this? (A funnel chart showing user count and conversion percentage at each step). How would you handle a funnel where steps can be completed in any order?
A concrete example
A good pseudo-SQL structure would be a CTE user_cohort that gets user_ids from the last 30 days. Then a CTE user_funnel_steps that does SELECT user_id, MIN(CASE WHEN event_name = 'signup_complete' THEN timestamp END) as ts1, MIN(CASE WHEN event_name = 'profile_created' THEN timestamp END) as ts2, ... FROM events WHERE user_id IN (SELECT user_id FROM user_cohort) GROUP BY user_id. The final SELECT would use COUNT(ts1), COUNT(CASE WHEN ts2 > ts1 THEN 1 END), and COUNT(CASE WHEN ts3 > ts2 AND ts2 > ts1 THEN 1 END) to get the numbers for the final calculation.
Interview question
To accurately measure a multi-step user onboarding funnel in SQL, which strategy best handles repeat actions and ensures sequential step completion?
- a.Count distinct users for each event type separately and combine the results with joins.
- b.Order all user events by timestamp using a window function, then filter for the first occurrence of each event.
- c.Use conditional aggregation (MIN(CASE WHEN...)) to capture the first timestamp for each step, then apply sequential timestamp checks.Correct
- d.Aggregate all event timestamps for each user and check if the maximum timestamp for a later step is greater than the maximum for an earlier step.
Why? this is the answer
The recommended robust approach involves using MIN(CASE WHEN...) to get the first timestamp for each step, which handles repeat actions, and then checking for sequential timestamps (e.g., ts_step2 > ts_step1). Simply counting distinct users for each event independently fails to track a single user's journey.
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 analytics — each one lists the topics its interview covers.
See open roles