Write SQL for a signup → activation → purchase funnel, broken down by weekly signup cohort.

TL;DR: Collapse to one row per user with MIN(CASE...) first-time timestamps per step, gate each stage on the prior one's timestamp (purchase_ts > activate_ts > signup_ts), then aggregate by signup-week cohort. Counting events instead of users double-counts every step.

How to approach it

Pin down funnel semantics before writing SQL, this is where the evaluation actually happens: Must steps occur in order (purchase after activation)? Within a time window? Is the funnel user-level (did the user ever activate) or event-counting? Define the cohort: week of signup, and every user belongs to exactly one cohort. State that you'll compute one row per user with first-time timestamps per step, then aggregate.

A strong answer

WITH user_steps AS (
  SELECT
    user_id,
    MIN(CASE WHEN event = 'signup'   THEN event_ts END) AS signup_ts,
    MIN(CASE WHEN event = 'activate' THEN event_ts END) AS activate_ts,
    MIN(CASE WHEN event = 'purchase' THEN event_ts END) AS purchase_ts
  FROM events
  GROUP BY user_id
),
funnel AS (
  SELECT
    DATE_TRUNC('week', signup_ts) AS cohort_week,
    user_id,
    CASE WHEN activate_ts > signup_ts THEN 1 ELSE 0 END AS did_activate,
    CASE WHEN purchase_ts > activate_ts
          AND activate_ts > signup_ts THEN 1 ELSE 0 END AS did_purchase
  FROM user_steps
  WHERE signup_ts IS NOT NULL
)
SELECT
  cohort_week,
  COUNT(*)                                   AS signed_up,
  SUM(did_activate)                          AS activated,
  SUM(did_purchase)                          AS purchased,
  ROUND(100.0 * SUM(did_activate) / COUNT(*), 1)        AS activation_pct,
  ROUND(100.0 * SUM(did_purchase) / NULLIF(SUM(did_activate),0), 1) AS purchase_pct_of_activated
FROM funnel
GROUP BY cohort_week
ORDER BY cohort_week;

The design decisions to narrate:

• Pivot to one row per user first (MIN(CASE...)). Funnels are user-level facts; aggregating raw events directly double-counts repeat purchasers.
• Enforce ordering with activate_ts > signup_ts etc., a user who purchased before activating (data weirdness, imports) shouldn't count as funnel completion. NULL comparisons conveniently evaluate to not-true, so missing steps drop out, but say that explicitly so it's clearly intentional.
• Report both conversion bases: step-over-previous-step and step-over-cohort; analysts ask for both, and NULLIF guards the division.

Three users are enough to spring both traps at once (both queries executed against the same fixture). User 1 signs up, activates, then purchases three times. User 2 signs up, purchases, then activates, an import artifact or a migrated account. User 3 only signs up. Count events naively and the "funnel" reads 3 signups, 2 activations, 4 purchases: more purchases than signups, a conversion rate over 100%, and a chart any PM will bounce back within the hour. The user-level ordered version returns 3, 2, 1: user 1's three purchases collapse to one converting user, and user 2's out-of-order purchase correctly fails the p > a > s gate. Notice the two errors pushed in opposite directions (repeat events inflate, out-of-order events sneak through), which is why fixing only the double-count still leaves a wrong funnel and the ordering predicate has to be there too.

Offer the windowed variant if asked for "within 7 days of signup": add AND activate_ts <= DATEADD(day, 7, signup_ts), and note that unbounded funnels make recent cohorts look artificially bad, so a fixed window is usually the right default for cohort comparison. That observation is a strong product-sense signal.

What interviewers probe next

• "Why are last week's numbers so low?", right-censoring: recent cohorts haven't had time to convert; fixed windows or maturity filters (cohort_week <= today − 7d).
• "Repeat purchases / revenue per cohort?", switch the purchase CTE to aggregates instead of MIN.
• "Now retention by week since signup", the cohort-retention matrix; same skeleton with a weeks-since-signup dimension.
• Scale: events tables are huge, partition prune on event date, pre-filter to the three event types before the GROUP BY.

Common mistakes

• Counting events instead of users (the classic double-count).
• Joining events to events per step, three self-joins that fan out and run forever.
• Ignoring step order entirely.
• No NULLIF on conversion division, blowing up on empty cohorts.