SQL Interview Prep · Lesson

Building a Multi-Step Funnel

Counting users who reach each ordered step and computing step conversion rates.

Building a Multi-Step Funnel is a free SQL Interview Prep lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What a Funnel Question Really Tests

When an interviewer says "build me a signup funnel", they are testing whether you can count distinct users who reach each ordered step and express drop-off between them.

A funnel has stages like visit -> signup -> activate -> purchase. The deliverable is usually one row per step with a user count and a conversion rate.

Count users, not events (one user firing an event twice is still one user).
Steps are ordered; reaching step 3 implies you passed steps 1 and 2.

The Event Table You Will Be Given

Almost every funnel question hands you a single events table in long form. Picture this shape:

user_id who did the action
event_name such as 'visit', 'signup', 'purchase'
event_time a timestamp

One row per action. Your job is to reshape this into a step-by-step count. Always confirm the exact event names with the interviewer before writing SQL.

CREATE TABLE events (
  user_id    INT,
  event_name VARCHAR(50),
  event_time TIMESTAMP
);

Counting Users at One Step

Start simple: how many distinct users reached a single step? Use COUNT(DISTINCT user_id) with a filter on the event name.

This is the building block for every funnel. If you can count one step cleanly, you can count them all.

SELECT COUNT(DISTINCT user_id) AS users_who_signed_up
FROM events
WHERE event_name = 'signup';

Conditional Aggregation for All Steps

The clean interview answer counts every step in one pass using conditional aggregation: a CASE inside COUNT(DISTINCT ...).

For each step, count the distinct users whose event matches that step. One scan, one row of step totals.

SELECT
  COUNT(DISTINCT CASE WHEN event_name = 'visit'    THEN user_id END) AS step1_visit,
  COUNT(DISTINCT CASE WHEN event_name = 'signup'   THEN user_id END) AS step2_signup,
  COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) AS step3_purchase
FROM events;

The Hidden Bug: Steps Are Not Ordered

The query you just saw has a trap interviewers love. It counts anyone who fired 'purchase', even if they never visited or signed up in the data.

A real funnel requires each later step to be a subset of the earlier step. Counting events independently can produce step 3 larger than step 2, which is logically impossible for a funnel.

The fix: tie each user's steps together, usually by collapsing to one row per user first.

One Row Per User With Flags

The robust pattern: collapse the event log to one row per user, with a boolean flag (as 0/1) for whether they ever did each step. MAX(CASE ...) turns the long log into a wide per-user summary.

WITH user_steps AS (
  SELECT
    user_id,
    MAX(CASE WHEN event_name = 'visit'    THEN 1 ELSE 0 END) AS did_visit,
    MAX(CASE WHEN event_name = 'signup'   THEN 1 ELSE 0 END) AS did_signup,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS did_purchase
  FROM events
  GROUP BY user_id
)
SELECT * FROM user_steps;

Enforcing Step Order

Now enforce the funnel rule: a user only counts for step N if they also did every step before it. Reaching 'purchase' only matters if they also visited and signed up.

Sum the flags with the prerequisite conditions ANDed together so each step is a true subset of the prior.

WITH user_steps AS (
  SELECT
    user_id,
    MAX(CASE WHEN event_name = 'visit'    THEN 1 ELSE 0 END) AS did_visit,
    MAX(CASE WHEN event_name = 'signup'   THEN 1 ELSE 0 END) AS did_signup,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS did_purchase
  FROM events
  GROUP BY user_id
)
SELECT
  SUM(did_visit)                                       AS step1_visit,
  SUM(CASE WHEN did_visit = 1 AND did_signup = 1 THEN 1 ELSE 0 END)                       AS step2_signup,
  SUM(CASE WHEN did_visit = 1 AND did_signup = 1 AND did_purchase = 1 THEN 1 ELSE 0 END)  AS step3_purchase
FROM user_steps;

Turning Counts Into a Tidy Long Result

Interviewers often prefer one row per step rather than one wide row. Pivot the wide totals into long form with a small UNION ALL, attaching a step number for ordering.

This makes conversion-rate math and charting much easier in the next step.

WITH funnel AS (
  SELECT 1 AS step_no, 'visit'    AS step_name, 1000 AS users UNION ALL
  SELECT 2,           'signup',                   420  UNION ALL
  SELECT 3,           'purchase',                 95
)
SELECT step_no, step_name, users
FROM funnel
ORDER BY step_no;

Step-to-Step Conversion Rate

Two rates matter and interviewers will ask which you mean:

Step conversion: users at this step divided by users at the previous step.
Overall conversion: users at this step divided by the top of the funnel.

Use LAG to grab the previous step's count for the step-to-step rate. Cast to a decimal so you do not get integer division.

WITH funnel AS (
  SELECT 1 AS step_no, 'visit'    AS step_name, 1000 AS users UNION ALL
  SELECT 2,           'signup',                   420  UNION ALL
  SELECT 3,           'purchase',                 95
)
SELECT
  step_name,
  users,
  ROUND(100.0 * users / LAG(users) OVER (ORDER BY step_no), 1) AS step_conv_pct
FROM funnel
ORDER BY step_no;

Overall Conversion From the Top

For overall conversion, divide each step by the first step's count. FIRST_VALUE over the ordered funnel pins that top number for every row.

Always mention to the interviewer that you guarded against integer division by multiplying by 100.0.

WITH funnel AS (
  SELECT 1 AS step_no, 'visit'    AS step_name, 1000 AS users UNION ALL
  SELECT 2,           'signup',                   420  UNION ALL
  SELECT 3,           'purchase',                 95
)
SELECT
  step_name,
  users,
  ROUND(100.0 * users / FIRST_VALUE(users) OVER (ORDER BY step_no), 1) AS overall_pct
FROM funnel
ORDER BY step_no;

Putting the Whole Funnel Together

Here is the end-to-end answer interviewers want: collapse to per-user flags, enforce order, unpivot to long form, then compute both rates. Walk through it out loud, naming each CTE's purpose.

This structure scales: add a step by adding one flag and one UNION ALL row.

WITH user_steps AS (
  SELECT user_id,
    MAX(CASE WHEN event_name = 'visit'    THEN 1 ELSE 0 END) AS s1,
    MAX(CASE WHEN event_name = 'signup'   THEN 1 ELSE 0 END) AS s2,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS s3
  FROM events GROUP BY user_id
),
totals AS (
  SELECT 1 AS step_no, 'visit'    AS step_name, SUM(s1) AS users FROM user_steps UNION ALL
  SELECT 2, 'signup',   SUM(CASE WHEN s1=1 AND s2=1 THEN 1 ELSE 0 END) FROM user_steps UNION ALL
  SELECT 3, 'purchase', SUM(CASE WHEN s1=1 AND s2=1 AND s3=1 THEN 1 ELSE 0 END) FROM user_steps
)
SELECT step_name, users,
  ROUND(100.0 * users / LAG(users) OVER (ORDER BY step_no), 1) AS step_pct
FROM totals ORDER BY step_no;

Quick Check

An interviewer notices your funnel shows 95 users at 'purchase' but only 80 at 'signup'. What is the most likely cause?

Recap: Multi-Step Funnels

You can now build a funnel the way interviews expect:

Count distinct users per ordered step, never raw events.
Collapse the event log to one row per user with MAX(CASE ...) flags.
Enforce order so each step is a subset of the previous one.
Compute step-to-step (LAG) and overall (FIRST_VALUE) conversion, guarding against integer division.

Next: making sure those steps actually happened in the right sequence and within a time window.

Frequently asked questions

Is the “Building a Multi-Step Funnel” lesson free?

Yes — the full text of “Building a Multi-Step Funnel” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Building a Multi-Step Funnel”?

Counting users who reach each ordered step and computing step conversion rates. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Building a Multi-Step Funnel” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

Building a Multi-Step Funnel
Ordered Events and Time Windows
A/B Test Assignment and Metrics
Lift, Significance and Guardrails in SQL

← Back to SQL Interview Prep