early-tent-72446
09/17/2021, 8:03 AMfuture-teacher-7046
early-tent-72446
09/17/2021, 10:19 AMSELECT TRIM(user_id::varchar) AS user_id,
TRIM(user_id::varchar) AS anonymous_id,
TRIM(test_id) AS experiment_id,
TRIM(variant_label) AS variation_id,
CASE WHEN da.created_at < experiment_start_time THEN 'returning' ELSE 'new' END AS user_cohort,
MIN(timestamp) AS timestamp
FROM rpt.segment_ab_test
JOIN dwh.dim_account da USING (account_id)
JOIN (
SELECT test_id, MIN(timestamp) AS experiment_start_time
FROM rpt.segment_ab_test
GROUP BY 1) exp_start USING (test_id)
GROUP BY 1, 2, 3, 4, 5