How to calculate a funnel in SQL
Contents:
Why analysts build funnels in SQL
A funnel is the most useful diagnostic a product team has. Without one you see "checkout conversion dropped from 5.0 to 3.2 percent" with nowhere to look. With one the picture sharpens: landing → product → cart is flat, cart → checkout collapsed from 60 to 38 percent on Tuesday. Now the question is narrow — what shipped Tuesday that touches checkout?
A PM at Stripe, DoorDash, or Notion pages you on Sunday for a "quick funnel" because the alternative is flying blind during a launch. The honest answer comes from a funnel cut by segment and channel.
Building a correct funnel is harder than it looks. A naive COUNT(DISTINCT user_id) per event ignores ordering — a user who bought in January and viewed the landing in April gets counted as completed. This post covers nine recipes against events(user_id, event_name, event_at) and, where noted, users(user_id, attribution_channel).
What a funnel actually is
A funnel is an ordered sequence of events. At each step users drop off; the product of step-to-step rates is the end-to-end rate. Three canonical shapes: e-commerce (landing → product → cart → checkout → paid), SaaS (signup → confirm → login → activation → paid), mobile (install → open → onboarding → main → purchase).
The unit of analysis matters as much as the steps. Product funnels count distinct users, engagement funnels count sessions, retry funnels count attempts. Mixing units in one query is the most common bug.
The SQL recipes
1. Loose funnel without ordering
The simplest funnel counts distinct users per step inside a month. Fast for monitoring, dangerous as a diagnostic — it ignores ordering.
SELECT
COUNT(DISTINCT CASE WHEN event_name = 'landing_view' THEN user_id END) AS step1,
COUNT(DISTINCT CASE WHEN event_name = 'product_view' THEN user_id END) AS step2,
COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart' THEN user_id END) AS step3,
COUNT(DISTINCT CASE WHEN event_name = 'checkout_start' THEN user_id END) AS step4,
COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) AS step5
FROM events
WHERE event_at >= '2026-04-01'
AND event_at < '2026-05-01';A user who purchased last month and viewed the landing this month gets counted on both step1 and step5. Acceptable noise for monitoring, not for a launch postmortem.
2. Strict ordered funnel
When accuracy matters, each step is computed against the previous step's timestamp. A user only counts on step N if they completed steps 1..N-1 in order.
WITH step1 AS (
SELECT user_id, MIN(event_at) AS step1_at
FROM events WHERE event_name = 'landing_view' GROUP BY user_id
),
step2 AS (
SELECT s1.user_id, MIN(e.event_at) AS step2_at
FROM step1 s1
JOIN events e ON e.user_id = s1.user_id
AND e.event_name = 'product_view'
AND e.event_at > s1.step1_at
GROUP BY s1.user_id
),
step3 AS (
SELECT s2.user_id, MIN(e.event_at) AS step3_at
FROM step2 s2
JOIN events e ON e.user_id = s2.user_id
AND e.event_name = 'add_to_cart'
AND e.event_at > s2.step2_at
GROUP BY s2.user_id
),
step4 AS (
SELECT s3.user_id, MIN(e.event_at) AS step4_at
FROM step3 s3
JOIN events e ON e.user_id = s3.user_id
AND e.event_name = 'purchase'
AND e.event_at > s3.step3_at
GROUP BY s3.user_id
)
SELECT
(SELECT COUNT(*) FROM step1) AS step1,
(SELECT COUNT(*) FROM step2) AS step2,
(SELECT COUNT(*) FROM step3) AS step3,
(SELECT COUNT(*) FROM step4) AS step4;The cost is N self-joins; the benefit is a defensible number.
3. Funnel inside a time window
For consumer products you care about a single shopping intent, usually 24 hours. Compute the minimum timestamp per event per user; require each step inside the window.
WITH first_events AS (
SELECT
user_id,
MIN(CASE WHEN event_name = 'landing_view' THEN event_at END) AS t1,
MIN(CASE WHEN event_name = 'product_view' THEN event_at END) AS t2,
MIN(CASE WHEN event_name = 'add_to_cart' THEN event_at END) AS t3,
MIN(CASE WHEN event_name = 'purchase' THEN event_at END) AS t4
FROM events
GROUP BY user_id
)
SELECT
COUNT(CASE WHEN t1 IS NOT NULL THEN 1 END) AS step1,
COUNT(CASE WHEN t2 > t1 AND t2 < t1 + INTERVAL '24 hours' THEN 1 END) AS step2,
COUNT(CASE WHEN t3 > t2 AND t3 < t1 + INTERVAL '24 hours' THEN 1 END) AS step3,
COUNT(CASE WHEN t4 > t3 AND t4 < t1 + INTERVAL '24 hours' THEN 1 END) AS step4
FROM first_events;The window anchors on t1, not each previous step — a common interview follow-up.
4. Step-to-step conversion rates
Counts are not actionable; rates are. Wrap the funnel in a CTE and divide consecutive steps, guarding division with NULLIF.
WITH funnel AS (
SELECT
COUNT(DISTINCT CASE WHEN event_name = 'landing_view' THEN user_id END) AS step1,
COUNT(DISTINCT CASE WHEN event_name = 'product_view' THEN user_id END) AS step2,
COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart' THEN user_id END) AS step3,
COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) AS step4
FROM events
)
SELECT
step1,
step2,
step3,
step4,
100.0 * step2 / NULLIF(step1, 0) AS cr_1_2,
100.0 * step3 / NULLIF(step2, 0) AS cr_2_3,
100.0 * step4 / NULLIF(step3, 0) AS cr_3_4,
100.0 * step4 / NULLIF(step1, 0) AS cr_total
FROM funnel;5. Cohorted funnel
Conversion varies by cohort. A 2024 viral-moment cohort behaves nothing like a 2026 paid-search cohort. Group by cohort_month to see each generation separately.
WITH cohorts AS (
SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
FROM events WHERE event_name = 'signup'
GROUP BY user_id
),
funnel_by_cohort AS (
SELECT
c.cohort_month,
COUNT(DISTINCT c.user_id) AS signups,
COUNT(DISTINCT CASE WHEN e.event_name = 'activation' THEN c.user_id END) AS activated,
COUNT(DISTINCT CASE WHEN e.event_name = 'purchase' THEN c.user_id END) AS paid
FROM cohorts c
LEFT JOIN events e ON e.user_id = c.user_id
GROUP BY c.cohort_month
)
SELECT
cohort_month,
signups,
activated,
paid,
100.0 * activated / NULLIF(signups, 0) AS cr_activation,
100.0 * paid / NULLIF(signups, 0) AS cr_paid
FROM funnel_by_cohort
ORDER BY cohort_month;6. Funnel by segment
The most common follow-up after a topline funnel is "how does it look by channel?" Join users with attribution and group on it.
SELECT
u.attribution_channel,
COUNT(DISTINCT u.user_id) AS signups,
COUNT(DISTINCT CASE WHEN e.event_name = 'activation' THEN u.user_id END) AS activated,
COUNT(DISTINCT CASE WHEN e.event_name = 'purchase' THEN u.user_id END) AS paid,
100.0 * COUNT(DISTINCT CASE WHEN e.event_name = 'activation' THEN u.user_id END)
/ NULLIF(COUNT(DISTINCT u.user_id), 0) AS cr_activation
FROM users u
LEFT JOIN events e ON e.user_id = u.user_id
GROUP BY u.attribution_channel
ORDER BY cr_activation DESC;7. Time-to-convert
Step counts hide latency. Two products with 30 percent activation can have very different UX if one activates in 10 minutes and the other in 10 days. Compute the gap between step timestamps; report mean and median.
WITH user_funnel AS (
SELECT
user_id,
MIN(CASE WHEN event_name = 'signup' THEN event_at END) AS signup_at,
MIN(CASE WHEN event_name = 'activation' THEN event_at END) AS activation_at,
MIN(CASE WHEN event_name = 'purchase' THEN event_at END) AS purchase_at
FROM events
GROUP BY user_id
)
SELECT
AVG(EXTRACT(EPOCH FROM (activation_at - signup_at)) / 3600) AS avg_hrs_to_activate,
AVG(EXTRACT(EPOCH FROM (purchase_at - signup_at)) / 3600) AS avg_hrs_to_purchase,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY
EXTRACT(EPOCH FROM (activation_at - signup_at)) / 3600) AS median_hrs_to_activate
FROM user_funnel
WHERE activation_at IS NOT NULL
AND signup_at IS NOT NULL;8. Session funnel
Some funnels only make sense inside one session — checkout where browsing yesterday should not count toward buying today. Group by session_id and require all steps inside the same session.
WITH sessions AS (
SELECT
user_id,
session_id,
event_name,
event_at
FROM events
WHERE event_at >= '2026-04-01'
),
session_funnel AS (
SELECT
session_id,
MAX(CASE WHEN event_name = 'landing_view' THEN 1 ELSE 0 END) AS has_step1,
MAX(CASE WHEN event_name = 'product_view' THEN 1 ELSE 0 END) AS has_step2,
MAX(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) AS has_step3,
MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS has_step4
FROM sessions
GROUP BY session_id
)
SELECT
SUM(has_step1) AS step1,
SUM(has_step1 * has_step2) AS step2,
SUM(has_step1 * has_step2 * has_step3) AS step3,
SUM(has_step1 * has_step2 * has_step3 * has_step4) AS step4
FROM session_funnel;Multiplying boolean indicators is the fastest way to count ordered chains without N self-joins.
9. Drop-off analysis
Drop-off is the mirror of conversion and often more revealing. Instead of "how many got to step 4," ask "how many got stuck at step 2." A spike at one step is the strongest signal for where to investigate first.
WITH user_progress AS (
SELECT
user_id,
MAX(CASE WHEN event_name = 'step1' THEN 1 ELSE 0 END) AS s1,
MAX(CASE WHEN event_name = 'step2' THEN 1 ELSE 0 END) AS s2,
MAX(CASE WHEN event_name = 'step3' THEN 1 ELSE 0 END) AS s3,
MAX(CASE WHEN event_name = 'step4' THEN 1 ELSE 0 END) AS s4
FROM events
GROUP BY user_id
)
SELECT
SUM(CASE WHEN s1=1 AND s2=0 THEN 1 ELSE 0 END) AS dropped_at_step1,
SUM(CASE WHEN s2=1 AND s3=0 THEN 1 ELSE 0 END) AS dropped_at_step2,
SUM(CASE WHEN s3=1 AND s4=0 THEN 1 ELSE 0 END) AS dropped_at_step3,
SUM(CASE WHEN s4=1 THEN 1 ELSE 0 END) AS completed
FROM user_progress;Common pitfalls
The most common mistake on a first funnel is to ignore ordering and trust raw per-step distinct counts inside a date range. The query is one line and the number looks plausible, but silently includes users who completed step five before step one. The fix is to anchor each step on the previous step's timestamp via successive joins, as in recipe two.
Another trap is mixing units inside one query. Step one counts distinct users, step two accidentally counts events, and the step-to-step rate becomes a ratio of incompatible quantities. A user who tried checkout five times before paying contributes one to distinct users and five to events. Pick one unit per funnel.
Wrong time windows distort funnels longer than a week. A January signup converting in April should not count as a 30-day activation, yet naive queries lump them together. Anchor the window on the entry step. Window length is a product decision — 24 hours for consumer, 7 days for SaaS, 30 days for B2B — but it must be explicit.
A subtler mistake is silently dropping NULL timestamps. When activation_at is null, EXTRACT(EPOCH FROM (activation_at - signup_at)) returns null and AVG excludes it. The mean time-to-activate then describes only converted users — a survivorship-biased subset. State whether your latency metric is conditional on conversion.
Finally, teams cohort on the wrong event. Cohorting paid users on first purchase month and computing retention against signup behavior produces a chart that drifts every re-pull. Choose an immutable anchor — usually first signup — and stick with it.
Optimization tips
The strict funnel runs N self-joins; on a billion-row table that is slow. The highest-leverage change is to materialize a daily "first event of type" table — one row per user per event type with the minimum timestamp — and join recipes 2, 3, 5, 7 against it. Query cost drops an order of magnitude.
Partition events by date. Scanning 30 days of partitions is far cheaper than a six-month full scan. Snowflake and BigQuery give this via clustering; on Postgres use declarative partitioning by event_at::date. On Databricks, Z-order on user_id and event_at so join-heavy recipes prune file groups.
For dashboards, precompute. A scheduled job that materializes funnel counts per (cohort_month, channel, day) into a thin reporting table serves every PM variation in milliseconds. Recompute the last 7 days daily for late events.
For very large tables, replace strict funnels with MATCH_RECOGNIZE (Snowflake, Oracle) or warehouse pattern-detection functions. They express ordered sequences directly and the optimizer plans them better than chained self-joins.
Related reading
- SQL window functions interview questions
- How to calculate conversion window in SQL
- How to calculate customer journey in SQL
- A/B testing peeking mistake
If you want to drill ordered-event SQL on a daily cadence, NAILDD is launching with hundreds of SQL problems built around this pattern.
FAQ
How many steps belong in a typical funnel?
Four to seven. Fewer reports overall conversion, not a diagnosis. More than seven and the bottom becomes statistical noise. E-commerce: four to six. SaaS activation: five to eight. Mobile: four plus a separate re-engagement funnel.
Loose funnel or strict funnel?
Use a strict ordered funnel whenever a product decision depends on the number — postmortems, experiments, QBRs. Use loose funnels only on monitoring dashboards where you have validated the loose number tracks the strict one.
How do I pick the time window?
Match the window to user intent. Consumer e-commerce uses 24-hour or session windows. SaaS uses 7 to 14 days for activation. B2B sales runs 30 to 90 days. The wrong answer is picking a window because it makes your number look better — that ends in a credibility problem with leadership.
What should I do when I find a drop-off?
Triangulate before acting. A spike is a hypothesis, not a diagnosis. Pull session replays for dropped users, survey active ones, check whether the spike correlates with a release or campaign change. If the signal holds, design an A/B test against the suspected cause.
Should I count users, sessions, or events?
Match the unit to the decision. Onboarding funnels count distinct users. Engagement funnels count sessions. Payment retry funnels count attempts. Write the unit in a comment at the top of the query and never switch mid-funnel.
Can I run a funnel without a session ID?
Yes — recipe three shows how. Anchor on the first event per user and require subsequent steps to fall inside a fixed window. Not identical to a true session funnel, but close enough when the data lacks sessionization.