How to Build a Funnel Analysis Query in SQL

Every funnel builder I've used — Mixpanel, Amplitude, PostHog — is running the same operation underneath: count distinct users at each step, divide step N+1 by step N, render a bar chart. The UI is nice. But the query is not complicated, and if your events live in a database you can already query, you don't need to pipe them somewhere else to count them.

The problem is that the obvious SQL approach (self-join the events table once per funnel step) falls over on large tables. A five-step funnel means five joins against the same table. On a million-row events table that's fine. On fifty million rows it's a bad afternoon. This post covers both the straightforward approach and the one that actually scales.

The schema

Two tables. If yours are named differently, rename the columns in the CTEs and everything else works.

-- events: one row per tracked action
-- user_id, event_name, properties (jsonb), created_at
 
-- users: one row per account
-- id, signup_source, created_at, plan

The funnel we're building: signup → onboarding_complete → first_query → dashboard_created → subscription_started. Five steps, which is enough to surface where people actually drop off versus where you assume they do.

1. The basic funnel with LEFT JOINs

Start here. Each step is a subquery that finds the first occurrence of that event per user, then you LEFT JOIN them together so users who drop off still show up as NULLs rather than disappearing.

WITH step1 AS (
  SELECT u.id AS user_id, u.created_at AS signed_up_at
  FROM users u
  WHERE u.created_at >= '2026-07-01'
    AND u.created_at <  '2026-08-01'
),
step2 AS (
  SELECT user_id, MIN(created_at) AS completed_at
  FROM events
  WHERE event_name = 'onboarding_complete'
  GROUP BY user_id
),
step3 AS (
  SELECT user_id, MIN(created_at) AS completed_at
  FROM events
  WHERE event_name = 'first_query'
  GROUP BY user_id
),
step4 AS (
  SELECT user_id, MIN(created_at) AS completed_at
  FROM events
  WHERE event_name = 'dashboard_created'
  GROUP BY user_id
),
step5 AS (
  SELECT user_id, MIN(created_at) AS completed_at
  FROM events
  WHERE event_name = 'subscription_started'
  GROUP BY user_id
)
SELECT
  COUNT(*)                                          AS signups,
  COUNT(s2.user_id)                                 AS onboarded,
  COUNT(s3.user_id)                                 AS first_query,
  COUNT(s4.user_id)                                 AS dashboard,
  COUNT(s5.user_id)                                 AS subscribed,
  ROUND(100.0 * COUNT(s2.user_id) / NULLIF(COUNT(*), 0), 1)          AS signup_to_onboard_pct,
  ROUND(100.0 * COUNT(s3.user_id) / NULLIF(COUNT(s2.user_id), 0), 1) AS onboard_to_query_pct,
  ROUND(100.0 * COUNT(s4.user_id) / NULLIF(COUNT(s3.user_id), 0), 1) AS query_to_dashboard_pct,
  ROUND(100.0 * COUNT(s5.user_id) / NULLIF(COUNT(s4.user_id), 0), 1) AS dashboard_to_sub_pct
FROM step1 s1
LEFT JOIN step2 s2 ON s2.user_id = s1.user_id
LEFT JOIN step3 s3 ON s3.user_id = s1.user_id
LEFT JOIN step4 s4 ON s4.user_id = s1.user_id
LEFT JOIN step5 s5 ON s5.user_id = s1.user_id;

A few things to notice. MIN(created_at) handles repeat events — a user who hits the pricing page twelve times counts once, at their first visit. The LEFT JOIN chain means someone who signed up but never onboarded still appears in the signups count instead of vanishing from the result. And the step-to-step percentages (not just top-of-funnel percentages) tell you where the funnel breaks, which is the whole point.

The question people trip over: should step 3 only count users who also completed step 2? In this query, it doesn't — a user who somehow ran a query without finishing onboarding still shows up at step 3. That's a "loose" funnel. Whether that's right depends on your product. For most analyses it's what you want, because it tells you how many users reached each milestone regardless of path. Strict ordering is a different query, and we'll get there.

2. Time-bounded funnels

Raw "did they ever do it" funnels are misleading. A user who onboards nine months after signup isn't a conversion success story. Adding time windows tightens the signal:

WITH step1 AS (
  SELECT u.id AS user_id, u.created_at AS signed_up_at
  FROM users u
  WHERE u.created_at >= '2026-07-01'
    AND u.created_at <  '2026-08-01'
),
step2 AS (
  SELECT e.user_id, MIN(e.created_at) AS completed_at
  FROM events e
  JOIN step1 s1 ON s1.user_id = e.user_id
  WHERE e.event_name = 'onboarding_complete'
    AND e.created_at BETWEEN s1.signed_up_at
                        AND s1.signed_up_at + INTERVAL '24 hours'
  GROUP BY e.user_id
),
step3 AS (
  SELECT e.user_id, MIN(e.created_at) AS completed_at
  FROM events e
  JOIN step2 s2 ON s2.user_id = e.user_id
  WHERE e.event_name = 'first_query'
    AND e.created_at BETWEEN s2.completed_at
                        AND s2.completed_at + INTERVAL '7 days'
  GROUP BY e.user_id
)
SELECT
  (SELECT COUNT(*) FROM step1)          AS signups,
  (SELECT COUNT(*) FROM step2)          AS onboarded_24h,
  (SELECT COUNT(*) FROM step3)          AS queried_7d,
  ROUND(100.0 * (SELECT COUNT(*) FROM step2) /
    NULLIF((SELECT COUNT(*) FROM step1), 0), 1) AS pct_onboarded_24h,
  ROUND(100.0 * (SELECT COUNT(*) FROM step3) /
    NULLIF((SELECT COUNT(*) FROM step2), 0), 1) AS pct_queried_7d;

Each step's time window is anchored to the previous step's completion, not to signup. That matters. "Ran first query within 7 days of onboarding" is a different (and usually more useful) question than "ran first query within 7 days of signup." Pick the one that matches how your product actually works.

3. Funnel by segment

The overall conversion rate is a vanity number. What you actually want to know is whether paid-acquisition users convert differently from organic ones, or whether users on the free plan ever reach step 4. Add a GROUP BY on whatever dimension you care about:

WITH step1 AS (
  SELECT u.id AS user_id, u.signup_source, u.created_at AS signed_up_at
  FROM users u
  WHERE u.created_at >= '2026-07-01'
    AND u.created_at <  '2026-08-01'
),
step2 AS (
  SELECT user_id, MIN(created_at) AS completed_at
  FROM events
  WHERE event_name = 'onboarding_complete'
  GROUP BY user_id
),
step3 AS (
  SELECT user_id, MIN(created_at) AS completed_at
  FROM events
  WHERE event_name = 'first_query'
  GROUP BY user_id
)
SELECT
  s1.signup_source,
  COUNT(*)                AS signups,
  COUNT(s2.user_id)       AS onboarded,
  COUNT(s3.user_id)       AS first_query,
  ROUND(100.0 * COUNT(s2.user_id) / NULLIF(COUNT(*), 0), 1) AS onboard_pct,
  ROUND(100.0 * COUNT(s3.user_id) / NULLIF(COUNT(s2.user_id), 0), 1) AS query_pct
FROM step1 s1
LEFT JOIN step2 s2 ON s2.user_id = s1.user_id
LEFT JOIN step3 s3 ON s3.user_id = s1.user_id
GROUP BY s1.signup_source
ORDER BY signups DESC;

Swap signup_source for plan, country (if you have it in a properties jsonb column — properties->>'country'), UTM campaign, or anything else on the users table. This is where funnel analysis starts earning its keep. An overall 8% conversion rate that's actually 22% from organic and 3% from paid ads is a completely different story than the average suggests.

4. The strict-order funnel

All the queries above are "loose" — they check whether each event happened, but not whether it happened after the previous step. A user who created a dashboard before completing onboarding counts at both steps. Sometimes that's fine. Sometimes it's not.

Strict ordering requires a different structure. Use window functions to number each user's events chronologically, then check that the funnel steps appear in the right sequence:

WITH user_events AS (
  SELECT
    user_id,
    event_name,
    created_at,
    ROW_NUMBER() OVER (
      PARTITION BY user_id, event_name
      ORDER BY created_at
    ) AS event_occurrence
  FROM events
  WHERE event_name IN (
    'onboarding_complete', 'first_query',
    'dashboard_created', 'subscription_started'
  )
),
first_events AS (
  -- only keep each user's first occurrence of each event type
  SELECT user_id, event_name, created_at
  FROM user_events
  WHERE event_occurrence = 1
),
pivoted AS (
  SELECT
    user_id,
    MIN(created_at) FILTER (WHERE event_name = 'onboarding_complete')    AS onboard_at,
    MIN(created_at) FILTER (WHERE event_name = 'first_query')            AS query_at,
    MIN(created_at) FILTER (WHERE event_name = 'dashboard_created')      AS dashboard_at,
    MIN(created_at) FILTER (WHERE event_name = 'subscription_started')   AS sub_at
  FROM first_events
  GROUP BY user_id
),
cohort AS (
  SELECT u.id AS user_id
  FROM users u
  WHERE u.created_at >= '2026-07-01'
    AND u.created_at <  '2026-08-01'
)
SELECT
  COUNT(*)                                                                                AS signups,
  COUNT(*) FILTER (WHERE p.onboard_at IS NOT NULL)                                        AS onboarded,
  COUNT(*) FILTER (WHERE p.query_at IS NOT NULL AND p.query_at > p.onboard_at)            AS queried_after_onboard,
  COUNT(*) FILTER (WHERE p.dashboard_at IS NOT NULL AND p.dashboard_at > p.query_at
                     AND p.query_at > p.onboard_at)                                       AS dashboard_in_order,
  COUNT(*) FILTER (WHERE p.sub_at IS NOT NULL AND p.sub_at > p.dashboard_at
                     AND p.dashboard_at > p.query_at AND p.query_at > p.onboard_at)       AS subscribed_in_order
FROM cohort c
LEFT JOIN pivoted p ON p.user_id = c.user_id;

The strict funnel will always produce lower numbers than the loose one. If the gap between them is large, that's telling you something: users aren't following the path you designed. Maybe your onboarding flow has a skip button everyone clicks, or users discover the query editor before they "complete" onboarding. Both are worth knowing.

5. Conditional aggregation: the approach that actually scales

Here's the problem with everything above. Every CTE that looks up a step does a scan (or at least an index seek) against the events table. A five-step funnel is five passes. On tables with tens of millions of rows, the planner starts making choices you won't like — hash joins that spill to disk, nested loops on un-indexed filters, temp tables that blow through work_mem.

The fix is to scan the events table once and pivot with CASE WHEN:

WITH per_user AS (
  SELECT
    e.user_id,
    MIN(CASE WHEN e.event_name = 'onboarding_complete'    THEN e.created_at END) AS onboard_at,
    MIN(CASE WHEN e.event_name = 'first_query'            THEN e.created_at END) AS query_at,
    MIN(CASE WHEN e.event_name = 'dashboard_created'      THEN e.created_at END) AS dashboard_at,
    MIN(CASE WHEN e.event_name = 'subscription_started'   THEN e.created_at END) AS sub_at
  FROM events e
  WHERE e.event_name IN (
    'onboarding_complete', 'first_query',
    'dashboard_created', 'subscription_started'
  )
  GROUP BY e.user_id
),
cohort AS (
  SELECT u.id AS user_id, u.created_at AS signed_up_at
  FROM users u
  WHERE u.created_at >= '2026-07-01'
    AND u.created_at <  '2026-08-01'
)
SELECT
  COUNT(*)                                                           AS signups,
  COUNT(p.onboard_at)                                                AS onboarded,
  COUNT(p.query_at)                                                  AS first_query,
  COUNT(p.dashboard_at)                                              AS dashboard,
  COUNT(p.sub_at)                                                    AS subscribed,
  -- strict-order counts (events must happen in sequence)
  COUNT(*) FILTER (WHERE p.query_at > p.onboard_at)                  AS query_after_onboard,
  COUNT(*) FILTER (WHERE p.dashboard_at > p.query_at
                     AND p.query_at > p.onboard_at)                  AS dashboard_in_order,
  -- step-to-step conversion rates
  ROUND(100.0 * COUNT(p.onboard_at) / NULLIF(COUNT(*), 0), 1)       AS signup_to_onboard_pct,
  ROUND(100.0 * COUNT(p.query_at) / NULLIF(COUNT(p.onboard_at), 0), 1) AS onboard_to_query_pct
FROM cohort c
LEFT JOIN per_user p ON p.user_id = c.user_id;

One scan of events, one GROUP BY, one join to the cohort. The IN (...) filter lets Postgres use an index on (event_name, user_id, created_at) if you have one — and you should. On a 40M-row events table I've seen this run in under 2 seconds where the five-CTE version timed out at 30.

The CASE WHEN ... THEN created_at END returns NULL for events that don't match, and MIN ignores NULLs, so each column picks up only the first occurrence of its event type. You get loose counts, strict-order counts, and step-to-step rates all in one pass. Add time bounds by wrapping the CASE with an additional condition (AND e.created_at BETWEEN c.signed_up_at AND c.signed_up_at + INTERVAL '7 days'). Add segmentation by pulling signup_source into the cohort CTE and grouping by it.

This is the pattern I'd use in production. The LEFT JOIN version from section 1 is easier to read and fine for ad-hoc exploration. But if you're building a funnel query that runs on a schedule or powers a dashboard widget, conditional aggregation is the one that won't fall over when your events table grows.

Users who skip steps

One design decision every funnel query forces on you: what do you do with users who reach step 4 without doing step 2?

The loose funnel counts them at step 4. The strict funnel excludes them. Neither is "correct" — they answer different questions. The loose funnel asks "how many users reached this milestone." The strict funnel asks "how many users followed this specific path." If your onboarding flow lets users skip ahead, the strict funnel will undercount real engagement. If you're measuring the effectiveness of a designed sequence, the strict funnel is the honest one.

In practice, I run both and compare. A big gap between the two means your intended flow and your actual flow are different products.

Turning funnel queries into something people check

A funnel query that lives in someone's .sql file and runs when they remember to paste it into psql isn't really analytics. It's a query someone ran once. The part that makes it useful long-term is the same part that makes any SQL useful long-term: save it, schedule it, put it somewhere a team can see it without asking you.

Fastero turns any of these queries into a live dashboard widget — write the query, pin the result, set a refresh schedule. If you're already running product analytics from your database, funnel queries are the natural next step: they tell you not just how many users are active, but where in the journey they stall.

The conditional aggregation query from section 5 is particularly good for this. A single query that returns all five steps, both loose and strict counts, and step-to-step conversion rates gives you a complete funnel widget in one scheduled run. No orchestration, no five-query pipeline.

Try Fastero free — connect your database, build funnel queries in SQL or plain English, and pin them as live dashboard widgets. No credit card required.