Data Problem Solving and Business Context Questions

WITH users_cohort AS (
  SELECT user_id, signup_date
  FROM `project.dataset.users`
  -- assume users table is partitioned by signup_date
),

events_day30 AS (
  -- convert event_date -> candidate signup_date (event_date - 30 days)
  SELECT DISTINCT user_id,
         DATE_SUB(event_date, INTERVAL 30 DAY) AS signup_date
  FROM `project.dataset.events`
  WHERE event_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY) AND CURRENT_DATE()
  -- filter to recent range to limit scanned data; adjust as needed
),

active_on_day30 AS (
  -- keep only events that match an actual signup (inner join)
  SELECT e.signup_date, e.user_id
  FROM events_day30 e
  JOIN users_cohort u
    ON e.user_id = u.user_id
   AND e.signup_date = u.signup_date
)

SELECT
  u.signup_date,
  COUNT(*) AS cohort_size,
  COUNT(a.user_id) AS active_on_day30,
  SAFE_DIVIDE(COUNT(a.user_id), COUNT(*)) AS pct_active_day30
FROM users_cohort u
LEFT JOIN (
  SELECT signup_date, user_id FROM active_on_day30
  GROUP BY signup_date, user_id
) a
  ON u.signup_date = a.signup_date AND u.user_id = a.user_id
GROUP BY u.signup_date
ORDER BY u.signup_date;

WITH tx AS (
  SELECT
    transaction_id,
    user_id,
    amount,
    transaction_time::date AS tdate,
    CASE WHEN amount < 0 OR transaction_type = 'refund' THEN 1 ELSE 0 END AS is_refund
  FROM transactions
  WHERE transaction_time >= (CURRENT_DATE - INTERVAL '365 days')
)

, last_purchase AS (
  SELECT user_id,
         MAX(tdate) AS last_date
  FROM tx
  WHERE is_refund = 0
  GROUP BY user_id
)
SELECT
  u.user_id,
  COALESCE((CURRENT_DATE - lp.last_date), 365) AS recency_days
FROM (SELECT DISTINCT user_id FROM tx) u
LEFT JOIN last_purchase lp USING(user_id);

, freq AS (
  SELECT user_id,
         COUNT(*) FILTER (WHERE is_refund = 0) AS frequency_12m
  FROM tx
  GROUP BY user_id
)

, monet AS (
  SELECT user_id,
         SUM(amount) AS net_spend_12m
  FROM tx
  GROUP BY user_id
),
p99 AS (
  SELECT percentile_cont(0.99) WITHIN GROUP (ORDER BY net_spend_12m) AS p99_val
  FROM monet
)
SELECT m.user_id,
       LEAST(m.net_spend_12m, GREATEST(p.p99_val, 0)) AS monet_winsor_12m
FROM monet m CROSS JOIN p99 p;

WITH events_day AS (
  -- normalize to date (adjust timezone if needed)
  SELECT
    user_id,
    DATE(event_time) AS day,
    event_type,
    MIN(event_time) AS first_time -- earliest occurrence of this type on that day
  FROM events
  GROUP BY user_id, DATE(event_time), event_type
),
pivoted AS (
  -- pivot earliest times per user-day
  SELECT
    user_id,
    day,
    MAX(CASE WHEN event_type = 'page_view' THEN first_time END)  AS pv_time,
    MAX(CASE WHEN event_type = 'add_to_cart' THEN first_time END) AS atc_time,
    MAX(CASE WHEN event_type = 'checkout' THEN first_time END)    AS co_time
  FROM events_day
  GROUP BY user_id, day
),
flags AS (
  SELECT
    user_id,
    day,
    -- converted to add_to_cart if atc_time exists and occurs after pv_time
    CASE WHEN pv_time IS NOT NULL AND atc_time IS NOT NULL AND atc_time > pv_time THEN 1 ELSE 0 END AS pv_to_atc,
    -- converted to checkout if checkout occurs after add_to_cart
    CASE WHEN atc_time IS NOT NULL AND co_time IS NOT NULL AND co_time > atc_time THEN 1 ELSE 0 END AS atc_to_co,
    -- overall funnel: page_view -> add_to_cart -> checkout
    CASE WHEN pv_time IS NOT NULL AND atc_time IS NOT NULL AND co_time IS NOT NULL
              AND atc_time > pv_time AND co_time > atc_time THEN 1 ELSE 0 END AS pv_to_co
  FROM pivoted
)
SELECT
  day,
  COUNT(*) FILTER (WHERE pv_time IS NOT NULL) AS users_page_view,
  SUM(pv_to_atc) AS users_added_to_cart,
  SUM(atc_to_co) AS users_checked_out_from_atc,
  SUM(pv_to_co) AS users_full_funnel,
  -- conversion rates
  ROUND(100.0 * SUM(pv_to_atc) / NULLIF(COUNT(*) FILTER (WHERE pv_time IS NOT NULL),0),2) AS pct_pv_to_atc,
  ROUND(100.0 * SUM(atc_to_co) / NULLIF(SUM(pv_to_atc),0),2) AS pct_atc_to_co_from_atc,
  ROUND(100.0 * SUM(pv_to_co) / NULLIF(COUNT(*) FILTER (WHERE pv_time IS NOT NULL),0),2) AS pct_pv_to_co
FROM (
  -- need pv_time in outer aggregation; join pivoted to flags
  SELECT f.*, p.pv_time
  FROM flags f
  JOIN pivoted p USING (user_id, day)
) t
GROUP BY day
ORDER BY day;