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

TL;DR: Collapse to one row per user where each step is the first occurrence after the previous step (first activation after signup, first purchase after that activation), 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 signups AS (
  SELECT user_id, MIN(event_ts) AS signup_ts
  FROM events WHERE event = 'signup'
  GROUP BY user_id
),
activations AS (             -- first activation AFTER signup
  SELECT s.user_id, s.signup_ts, MIN(e.event_ts) AS activate_ts
  FROM signups s
  LEFT JOIN events e
    ON e.user_id = s.user_id AND e.event = 'activate'
   AND e.event_ts > s.signup_ts
  GROUP BY s.user_id, s.signup_ts
),
user_steps AS (              -- first purchase AFTER that activation
  SELECT a.user_id, a.signup_ts, a.activate_ts, MIN(e.event_ts) AS purchase_ts
  FROM activations a
  LEFT JOIN events e
    ON e.user_id = a.user_id AND e.event = 'purchase'
   AND e.event_ts > a.activate_ts
  GROUP BY a.user_id, a.signup_ts, a.activate_ts
)
SELECT
  DATE_TRUNC('week', signup_ts)              AS cohort_week,
  COUNT(*)                                   AS signed_up,
  COUNT(activate_ts)                         AS activated,
  COUNT(purchase_ts)                         AS purchased,
  ROUND(100.0 * COUNT(activate_ts) / COUNT(*), 1)                    AS activation_pct,
  ROUND(100.0 * COUNT(purchase_ts) / NULLIF(COUNT(activate_ts), 0), 1) AS purchase_pct_of_activated
FROM user_steps
GROUP BY 1
ORDER BY 1;

The design decisions to narrate:

• Pivot to one row per user first. Funnels are user-level facts; aggregating raw events directly double-counts repeat purchasers. Take each step as the first occurrence after the previous one, not each step's first occurrence on its own: a user who purchases, then activates, then purchases again did complete the funnel, and the tempting MIN(CASE WHEN event = 'purchase' ...) per column compares against the early purchase and drops them (executed in sqlite).
• Enforce ordering in the join conditions (e.event_ts > s.signup_ts), so a purchase before activation (data weirdness, imports) never counts as completion. A user with no qualifying later step gets NULL and COUNT(column) skips it; say that explicitly so it's clearly intentional. Decide ties out loud too: strict > drops a user whose signup and activation share a timestamp (one request, or second-resolution logs), so use >= if steps can legitimately coincide.
• 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 these are two independent ways to inflate the funnel (repeat events multiply purchases, out-of-order events let an unconverted user 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 e.event_ts <= DATEADD(day, 7, s.signup_ts) to the activation join and AND e.event_ts <= DATEADD(day, 7, a.signup_ts) to the purchase join, so every required step is bounded. Then note that unbounded funnels make recent cohorts look artificially bad (right-censoring), and a fixed window alone does not cure that: a user who signed up two days ago has not had seven days to convert. Use a fixed window and include only cohorts old enough to have completed it, which is 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; use a fixed conversion window together with a maturity filter so every included user has had the full window (signup_ts <= today − 7d for a 7-day funnel), or a censoring-aware method such as survival curves.
• "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.