SQL for a three-step onboarding funnel

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.