Recap — 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.

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 (...).

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:

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.