Recap — Funnel SQL Patterns

# Funnel SQL Patterns A funnel: a sequence of events a user must do in order. *Sign up → verify email → create first project → invite teammate*. You want conversion at each step. This is interviewer-favorite territory because there are several right answers, each with tradeoffs. ## Pattern 1 — The self-join approach Conceptually simplest. Join the events table to itself once per funnel step. ```sql SELECT COUNT(DISTINCT s.user_id) AS step1_signup, COUNT(DISTINCT v.user_id) AS step2_verified, COUNT(DISTINCT p.user_id) AS step3_first_project, COUNT(DISTINCT i.user_id) AS step4_invited_teammate FROM events s LEFT JOIN events v ON v.user_id = s.user_id AND v.event = 'email_verified' AND v.event_time > s.event_time LEFT JOIN events p ON p.user_id = s.user_id AND p.event = 'first_project' AND p.event_time > v.event_time LEFT JOIN events i ON i.user_id = s.user_id AND i.event = 'invited_teammate' AND i.event_time > p.event_time WHERE s.event = 'signup'; ``` Pros: easy to write, easy to read. Cons: blows up for funnels longer than 3–4 steps, hard to handle "step done within X days of previous step". ## Pattern 2 — The window-function approach For each user, find their first occurrence of each event using MIN(... ) FILTER (...). ```sql WITH user_steps AS ( SELECT user_id, MIN(event_time) FILTER (WHERE event = 'signup') AS t_signup, MIN(event_time) FILTER (WHERE event = 'email_verified') AS t_verified, MIN(event_time) FILTER (WHERE event = 'first_project') AS t_project, MIN(event_time) FILTER (WHERE event = 'invited_teammate') AS t_invited FROM events GROUP BY user_id ) SELECT COUNT(*) AS step1_signup, COUNT(*) FILTER (WHERE t_verified > t_signup) AS step2_verified, COUNT(*) FILTER (WHERE t_project > t_verified) AS step3_first_project, COUNT(*) FILTER (WHERE t_invited > t_project) AS step4_invited_teammate FROM user_steps WHERE t_signup IS NOT NULL; ``` Pros: scales to long funnels, easy to add time-window constraints (`t_verified - t_signup < INTERVAL '7 days'`), one pass over events. Cons: requires window/filter syntax (not in MySQL 5.x). ## Pattern 3 — The events-with-row-number approach When event order matters and you want a sequence, not a count: ```sql WITH ranked AS ( SELECT user_id, event, event_time, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) AS step_num FROM events ) SELECT step_num, event, COUNT(*) FROM ranked GROUP BY step_num, event ORDER BY step_num; ``` This lets you ask: *what's the most common 3rd event a user does?* Useful for funnel discovery (working out the funnel shape from data, not the other way round). ## Which pattern when? | Need | Best pattern | |---|---| | 3-step funnel, simple counts | Self-join | | Long funnel (5+ steps) | Window / FILTER | | Funnel with time constraints (within 7 days) | Window / FILTER | | Discovering the funnel from data | ROW_NUMBER ranking | | Drop-off cohort analysis | Window / FILTER + GROUP BY signup_month | ## The classic interview follow-up "What if users can skip step 2 and go directly to step 3?" The right SQL answer depends on the product semantics: - If skipping step 2 still counts as completing the funnel → drop the `t_verified > t_signup` constraint. - If you want to count both paths → use UNION on two variants of the query. - If skipping is rare and you want to highlight it → add a `COUNT(*) FILTER (WHERE t_project > t_signup AND t_verified IS NULL)` column. The interviewer is testing whether you understand the *product* semantics, not just SQL syntax. ## Takeaway The window/FILTER pattern is the one to memorize. It's flexible, scales, and handles 90% of real funnel questions. Self-joins are fine for whiteboard problems with 3 steps; never write a 6-way self-join in real life.