Funnel & Conversion Queries — Real Interview Examples

Funnels show up in roughly half of all senior product and growth analyst interviews. The reason isn't that interviewers love funnels. It's that funnel questions test three things in a single problem: SQL fluency (multi-step event pivoting), business judgment (defining the steps correctly), and analytical interpretation (knowing what a 70% drop means and what to do about it).

This guide walks through three real interview-style funnel questions, end-to-end. Each shows the question, the right query plan, the SQL, and what the senior interviewer is actually scoring.

The Two Funnel Shapes You Need To Know

Before any specific question — there are exactly two shapes of funnel in SQL interviews:

1. Presence funnel. "Did the user ever hit step X." Order doesn't matter. SQL: pivot events per user with MAX(CASE WHEN event = 'X' THEN 1 ELSE 0 END).

2. Sequential funnel. "Did the user hit step Y after step X." Order matters. SQL: use MIN(event_time) per step and enforce step2_time >= step1_time.

Presence is easier. Sequential is what senior interviewers usually want. Always clarify which they're asking for before writing SQL.

Question 1 — The Classic Signup Funnel

The prompt: "Given an events table tracking landing_view, signup_start, signup_complete, and activated events, compute the conversion rate at each step of the funnel for the last 30 days."

Step 1 — Clarify the framing

Senior moves before writing SQL:

• "Is this a presence funnel or sequential?" (Sequential — assume so for signup flows.)
• "What's a user — by user_id or by anonymous session?" (Usually user_id for signup flows, since the user_id exists from signup_start onward. For pre-signup steps, you'd need anonymous identifiers.)
• "What does 'activated' mean operationally?" (Specific to the product — could be first purchase, first feature use, etc.)

These clarifying questions take 60 seconds and earn senior credit.

Step 2 — The query

WITH per_user_steps AS (
  SELECT
    user_id,
    MIN(CASE WHEN event_name = 'landing_view'    THEN event_time END) AS t1_landed,
    MIN(CASE WHEN event_name = 'signup_start'    THEN event_time END) AS t2_started,
    MIN(CASE WHEN event_name = 'signup_complete' THEN event_time END) AS t3_completed,
    MIN(CASE WHEN event_name = 'activated'       THEN event_time END) AS t4_activated
  FROM events
  WHERE event_time >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY user_id
),
counts AS (
  SELECT
    COUNT(*) FILTER (WHERE t1_landed IS NOT NULL)                                                   AS step1_landed,
    COUNT(*) FILTER (WHERE t2_started   IS NOT NULL AND t2_started   >= t1_landed)                AS step2_started,
    COUNT(*) FILTER (WHERE t3_completed IS NOT NULL AND t3_completed >= t2_started)               AS step3_completed,
    COUNT(*) FILTER (WHERE t4_activated IS NOT NULL AND t4_activated >= t3_completed)             AS step4_activated
  FROM per_user_steps
)
SELECT
  step1_landed,
  step2_started,
  step3_completed,
  step4_activated,
  ROUND(step2_started::numeric    / NULLIF(step1_landed, 0)    * 100, 1) AS land_to_start_pct,
  ROUND(step3_completed::numeric  / NULLIF(step2_started, 0)   * 100, 1) AS start_to_complete_pct,
  ROUND(step4_activated::numeric  / NULLIF(step3_completed, 0) * 100, 1) AS complete_to_activate_pct,
  ROUND(step4_activated::numeric  / NULLIF(step1_landed, 0)    * 100, 1) AS overall_pct
FROM counts;

Step 3 — Interpret the result

The interview isn't over when the query runs. The interviewer will ask: "What does this tell you?"

A junior answer: "It shows the conversion rate at each step."

A senior answer: "Land-to-start is 32%, which is healthy for organic traffic. Start-to-complete is 71%, which is solid — the signup form isn't broken. The big drop is complete-to-activate at 28%. That tells me users are getting through signup but not getting to value. I'd want to know what 'activated' requires — is it an email verification, a profile completion, or a first action? That'd inform where to focus."

The senior signal: locating the biggest drop and proposing where to dig.

Question 2 — The E-Commerce Cart-To-Purchase Funnel

The prompt: "Compute the cart-to-purchase conversion rate, broken down by device platform."

Step 1 — Clarify

• "Are we measuring users or carts?" (Important — one user can have multiple carts.)
• "Does 'cart' mean any add-to-cart event, or only ones where the user proceeded to view-cart?" (Different definitions give very different numbers.)
• "Time window?" (Default to last 30 days if not specified, mention you're choosing this.)

Step 2 — The query

Let's go with the cart-level (not user-level) framing, since e-commerce typically cares about cart conversion.

WITH carts AS (
  SELECT
    cart_id,
    user_id,
    device_platform,
    MIN(CASE WHEN event_name = 'add_to_cart' THEN event_time END) AS cart_added_time,
    MIN(CASE WHEN event_name = 'view_cart'   THEN event_time END) AS cart_viewed_time,
    MIN(CASE WHEN event_name = 'checkout'    THEN event_time END) AS checkout_time,
    MIN(CASE WHEN event_name = 'purchase'    THEN event_time END) AS purchase_time
  FROM cart_events
  WHERE event_time >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY cart_id, user_id, device_platform
)
SELECT
  device_platform,
  COUNT(*) AS carts_created,
  COUNT(*) FILTER (WHERE purchase_time IS NOT NULL AND purchase_time >= cart_added_time) AS carts_purchased,
  ROUND(
    COUNT(*) FILTER (WHERE purchase_time IS NOT NULL AND purchase_time >= cart_added_time)::numeric
    / NULLIF(COUNT(*), 0)
    * 100,
    1
  ) AS conversion_rate
FROM carts
GROUP BY device_platform
ORDER BY conversion_rate DESC;

Step 3 — Interpret

A senior answer: "Desktop is converting at 18%, mobile at 9%. That gap is huge and consistent with what we'd expect — mobile checkout flows usually friction more. But I'd want to dig into whether mobile is bringing in lower-intent traffic (e.g., social ads at the top of funnel) or whether the checkout itself is the bottleneck. To distinguish, I'd look at view-cart-to-checkout vs. checkout-to-purchase. If the drop is in the second step, the checkout itself is the issue."

Senior signal: not stopping at the metric, but proposing the next slice.

Question 3 — The Subscription Trial-To-Paid Funnel

The prompt: "For SaaS subscriptions: compute the trial-to-paid conversion rate over time. Has it improved or regressed in the last 6 months?"

Step 1 — Clarify

• "What's a 'trial'? Anyone who signed up, or only those who entered payment info?" (Different products define this differently.)
• "When do we count someone as 'paid'? When their card is charged the first time, or when they reach end of trial without cancellation?"
• "Trial duration — fixed (14 days) or variable?"

Step 2 — The query

WITH trials AS (
  SELECT
    user_id,
    DATE_TRUNC('month', trial_start) AS cohort_month,
    trial_start,
    trial_end,
    converted_to_paid
  FROM subscriptions
  WHERE trial_start >= CURRENT_DATE - INTERVAL '6 months'
    AND trial_start <  CURRENT_DATE - INTERVAL '14 days'  -- exclude trials that haven't matured
)
SELECT
  cohort_month,
  COUNT(*) AS trials_started,
  COUNT(*) FILTER (WHERE converted_to_paid) AS trials_converted,
  ROUND(
    COUNT(*) FILTER (WHERE converted_to_paid)::numeric
    / NULLIF(COUNT(*), 0)
    * 100,
    1
  ) AS conversion_rate
FROM trials
GROUP BY cohort_month
ORDER BY cohort_month;

Step 3 — Interpret

A senior answer: "Jan and Feb cohorts converted around 38%. March dipped to 31%, then bounced back to 37% in April. May and June are at 34-35%. So we had one bad month, then recovered. The dip in March is worth investigating — was there a pricing change, a product change, an acquisition channel shift? If we ship a hypothesis test or feature change, I'd want to see at least 3 consecutive months of improvement before declaring it worked, given the natural month-to-month variance."

Senior signal: distinguishing noise from signal, proposing a measurement bar before claiming a win.

The Three Mistakes Funnel Candidates Make

1. Presence funnel by accident. Writing the SQL with MAX(CASE WHEN ...) when the interviewer wanted sequential. Sounds subtle but it changes the numbers — and the interviewer will catch it. Always clarify shape first.

2. Wrong denominator. A funnel step's denominator should be the previous step, not the top of the funnel. If "step 3 conversion" means "of users who hit step 2, what % hit step 3," you can't divide by step-1 counts. Easy to mix up under pressure.

3. Skipping the time enforcement. Sequential funnels need explicit step2_time >= step1_time checks. Without them, you'll count users who somehow hit step 3 before step 2 — which is data corruption you should be flagging, not silently including.

What To Practice

Pick three funnel scenarios (signup, checkout, trial-to-paid). For each, write the query both ways — presence and sequential. Then write a 2-paragraph interpretation that names the biggest drop and proposes the next slice.

Six queries plus interpretation paragraphs builds funnel fluency faster than any number of pure SQL drills. The interpretation muscle is what separates senior from junior — and it's the easiest one to skip.