Three-Step Ordered Funnel — Solution & ExplanationProblems / Three-Step Ordered Funnel / EditorialApproach

Stage 1. First signup per user: MIN(event_time) WHERE event_type = 'signup'.

Stage 2. Join activate events to stage 1 and keep MIN of those with event_time > signup_time. A user whose only activate precedes their signup — or coincides with it exactly — never enters this CTE.

Stage 3. Same shape, anchored on stage 2's activate_time.

Count the stages. Each CTE has one row per surviving user, so three scalar COUNT(*) subqueries produce the funnel row. The counts are necessarily non-increasing — a free sanity check.

Key InsightOrdered funnels are recursive time-anchored joins: each stage's qualification depends on a *derived* timestamp from the previous stage, not on the raw event log. That is why the loose-funnel idiom (three conditional distinct counts) overstates every stage — it would count a user who purchased first and signed up later. The chain-of-CTEs shape scales to any depth funnel, and "MIN of qualifying events becomes the next anchor" is the exact semantics of "did the steps in order".Hints

Chain the stages: stage N's CTE joins candidate events to stage N-1's anchor timestamp and keeps MIN(event_time) of those strictly after it. Three stages, three CTEs.

Anchoring on MIN matters twice over: the user's FIRST signup is the anchor even if they signed up again later, and the first QUALIFYING activate (not just any activate) anchors the purchase stage.

An activate at the exact same timestamp as the signup does not count — the comparison is strictly greater. The data plants one.

Users can hit a later-stage event before an earlier stage (activate before signup, purchase before activate). The join-plus-time-filter structure discards those automatically — no special-casing needed.

Solve Three-Step Ordered Funnel yourself →