SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

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…