Analytics Patterns · Funnel with a time window · range join
60. Pricing to checkout within an hour
Concept: Funnel with a time window · range joinshow
Funnel steps only count in order and within a window: step 2 must come after step 1 and no later than the deadline. Anchor each user on their first step-1 time, then LEFT JOIN their step-2 events with a range condition, so users who didn't convert keep a row with NULL.
Example (different data):
SELECT q.lead_id, q.quoted_at, MIN(s.signed_at) AS signed_at FROM (SELECT lead_id, MIN(sent_at) AS quoted_at FROM quotes GROUP BY lead_id) AS q LEFT JOIN signatures s ON s.lead_id = q.lead_id AND s.signed_at > q.quoted_at AND s.signed_at <= q.quoted_at + INTERVAL '7 days' GROUP BY q.lead_id, q.quoted_at;
Your task
Growth measures how fast pricing-page visitors convert: a /checkout view within 60 minutes of their first /pricing view.
For every user who viewed /pricing, return user_id, first_pricing_at (their first /pricing view) and minutes_to_checkout (whole minutes from first_pricing_at to their first /checkout view after it and at most 60 minutes later; 60 counts; NULL if there is none). Sort by user_id.
Starting Postgres in your browser…
Checking your access…