Approach: For each user in the last 30 days, find whether they had each funnel step (page_view → sign_up → purchase). Deduplicate multiple events per user per step by taking the earliest occurrence per user-step. Then count users who reached each step and compute conversion rates between steps.sql
WITH windowed AS (
SELECT user_id, event_name, occurred_at
FROM events
WHERE occurred_at >= now() - interval '30 days'
),
first_per_step AS (
-- earliest event time per user and step
SELECT user_id, event_name, MIN(occurred_at) AS ts
FROM windowed
GROUP BY user_id, event_name
),
pivoted AS (
-- one row per user with earliest timestamp for each funnel step
SELECT
user_id,
MAX(CASE WHEN event_name = 'page_view' THEN ts END) AS page_view_ts,
MAX(CASE WHEN event_name = 'sign_up' THEN ts END) AS sign_up_ts,
MAX(CASE WHEN event_name = 'purchase' THEN ts END) AS purchase_ts
FROM first_per_step
GROUP BY user_id
),
qualified AS (
-- enforce funnel order: a later step must occur after the earlier step
SELECT
user_id,
(page_view_ts IS NOT NULL) AS did_page_view,
(sign_up_ts IS NOT NULL AND page_view_ts IS NOT NULL AND sign_up_ts >= page_view_ts) AS did_sign_up,
(purchase_ts IS NOT NULL AND sign_up_ts IS NOT NULL AND purchase_ts >= sign_up_ts) AS did_purchase
FROM pivoted
)
SELECT
SUM(CASE WHEN did_page_view THEN 1 ELSE 0 END) AS users_page_view,
SUM(CASE WHEN did_sign_up THEN 1 ELSE 0 END) AS users_sign_up,
SUM(CASE WHEN did_purchase THEN 1 ELSE 0 END) AS users_purchase,
ROUND(100.0 * SUM(CASE WHEN did_sign_up THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN did_page_view THEN 1 ELSE 0 END),0),2) AS pct_sign_up_per_page_view,
ROUND(100.0 * SUM(CASE WHEN did_purchase THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN did_sign_up THEN 1 ELSE 0 END),0),2) AS pct_purchase_per_sign_up,
ROUND(100.0 * SUM(CASE WHEN did_purchase THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN did_page_view THEN 1 ELSE 0 END),0),2) AS pct_purchase_per_page_view
FROM qualified;
