SQL for a three-step onboarding funnel
Funnel SQL with ordering correctness.
Anchor the 30-day signup cohort, count distinct users reaching each later step in timestamp order; conversion is each step over the prior.
Counting any occurrence regardless of order.
WHAT THIS TESTS Whether you can translate a funnel into correct SQL that respects user identity, step ordering, and a cohort window, rather than naively counting events.
A GOOD ANSWER COVERS First pin the cohort: distinct user_ids whose signup_complete timestamp is within the last 30 days. Then, for each subsequent step, count distinct users who performed that step after their signup, and ideally after the previous step, so the sequence is honored, not just presence. A clean pattern is conditional aggregation: build one row per user with the min timestamp of each event, then count users where each successive timestamp exists and is greater than the prior. Conversion between two steps equals distinct users reaching the later step over distinct users at the earlier step. Always count distinct users, never raw events, because a user can fire an event many times.
PSEUDO-SQL WITH cohort AS (SELECT user_id, MIN(timestamp) AS signup_ts FROM events WHERE event_name = signup_complete AND timestamp >= NOW() minus 30 days GROUP BY user_id), steps AS (SELECT c.user_id, c.signup_ts, MIN(CASE WHEN e.event_name = profile_created AND e.timestamp >= c.signup_ts THEN e.timestamp END) AS profile_ts, MIN(CASE WHEN e.event_name = first_action_taken THEN e.timestamp END) AS action_ts FROM cohort c JOIN events e ON e.user_id = c.user_id GROUP BY c.user_id, c.signup_ts) SELECT COUNT(signup_ts) AS signed_up, COUNT(profile_ts) AS created_profile, COUNT(CASE WHEN action_ts >= profile_ts THEN 1 END) AS took_action FROM steps. Conversion step1to2 is created_profile over signed_up, step2to3 is took_action over created_profile.
COMMON WRONG ANSWERS Counting events instead of distinct users. Ignoring timestamp order so a profile created before signup counts. Dividing every step by the total cohort rather than the immediately preceding step. Forgetting the 30-day cohort filter.
ONE CONCRETE EXAMPLE Of 10,000 recent signups, 6,500 created a profile and 4,000 of those took a first action, giving 65 percent and roughly 62 percent step conversions, which pinpoints profile creation as the larger leak to fix first.
Interview question
In a multi-step funnel query, why must you count distinct users and enforce timestamp ordering between steps?
- a.To reduce the number of rows scanned and speed up the query
- b.Because SQL cannot aggregate events without a DISTINCT clause
- c.So a user firing an event repeatedly or out of order is not miscounted as convertingCorrect
- d.To allow each step to be divided by the total cohort size
Why? this is the answer
Distinct users prevent double counting and ordering ensures the sequence is real, not an out-of-order event. The other options are wrong: this is about correctness, not speed, and steps divide by the prior step, not the total.
Just read this? Test yourself on what you have been reading.
Read the original → quadratichq.com
- #sql
- #funnel-analysis
- #conversion
- #cohort
- #analytics
Put your scrolling time to good use
Learn one idea, try a quiz and save useful cards for revision. Tezvyn makes it easy to learn and stay current in your tech field, a few minutes at a time.
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