Ordered Events and Time Windows
Ensuring steps happen in sequence and within a time limit using window functions.
Why Order and Time Matter
The basic funnel from the previous lesson only checks whether a user did each step. A sharper interviewer asks: did the steps happen in the right order, and within a reasonable time?
A user who purchased on Monday and visited the marketing page on Friday did not convert through your funnel. Sequence and timing turn a naive flag-based funnel into a credible one.
The Per-User First-Timestamp Idea
To reason about order, capture each user's first time at each step. The earliest visit, the earliest signup, the earliest purchase.
Then a clean conversion means first_signup_time >= first_visit_time and so on down the chain. MIN(event_time) grouped per step gives you those anchors.
SELECT
user_id,
MIN(CASE WHEN event_name = 'visit' THEN event_time END) AS first_visit,
MIN(CASE WHEN event_name = 'signup' THEN event_time END) AS first_signup,
MIN(CASE WHEN event_name = 'purchase' THEN event_time END) AS first_purchase
FROM events
GROUP BY user_id;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