Applying Data Science Techniques to Business Problems Questions

WITH params AS (
  SELECT '2025-01-01'::timestamptz AS start_dt, '2025-01-31'::timestamptz AS end_dt
),
-- keep only events in range and relevant steps
filtered AS (
  SELECT e.user_id, e.event_name, e.event_time
  FROM events e
  JOIN params p ON e.event_time >= p.start_dt AND e.event_time < p.end_dt
  WHERE e.event_name IN ('visit','signup','add_to_cart','checkout')
),
-- for each user and step, keep the earliest occurrence in the window
first_per_step AS (
  SELECT user_id, event_name, MIN(event_time) AS first_time
  FROM filtered
  GROUP BY user_id, event_name
),
-- pivot counts per step: whether a user reached a step
user_steps AS (
  SELECT user_id,
    MAX(CASE WHEN event_name='visit' THEN 1 ELSE 0 END) AS did_visit,
    MAX(CASE WHEN event_name='signup' THEN 1 ELSE 0 END) AS did_signup,
    MAX(CASE WHEN event_name='add_to_cart' THEN 1 ELSE 0 END) AS did_add_to_cart,
    MAX(CASE WHEN event_name='checkout' THEN 1 ELSE 0 END) AS did_checkout
  FROM first_per_step
  GROUP BY user_id
),
-- aggregate totals
totals AS (
  SELECT
    SUM(did_visit) AS visits,
    SUM(did_signup) AS signups,
    SUM(did_add_to_cart) AS adds,
    SUM(did_checkout) AS checkouts
  FROM user_steps
)
SELECT
  visits, signups, adds AS add_to_carts, checkouts,
  -- step-to-step conversion rates (guard against divide by zero)
  CASE WHEN visits>0 THEN round(signups::numeric / visits * 100, 2) ELSE NULL END AS visit_to_signup_pct,
  CASE WHEN signups>0 THEN round(adds::numeric / signups * 100, 2) ELSE NULL END AS signup_to_add_to_cart_pct,
  CASE WHEN adds>0 THEN round(checkouts::numeric / adds * 100, 2) ELSE NULL END AS add_to_cart_to_checkout_pct,
  CASE WHEN visits>0 THEN round(checkouts::numeric / visits * 100, 2) ELSE NULL END AS overall_visit_to_checkout_pct
FROM totals;