-- Initial Offer Viewed (binomial)
WITH __identities as (
  -- ERROR: Missing User Id Join Table!
  SELECT
    '' as anonymous_id,
    '' as user_id
),
__rawExperiment as (
  SELECT
    user_id,
    user_id as anonymous_id,
    enroll_time as timestamp,
    abtest_name as experiment_id,
    enrolled as variation_id,
    '' as device_id
  FROM
    magic.mv_abtest_users
),
__experiment as (
  -- Viewed Experiment
  SELECT
    e.anonymous_id as user_id,
    toString(e.variation_id) as variation,
    e.timestamp as conversion_start,
    dateAdd(hour, 72, e.timestamp) as conversion_end
  FROM
    __rawExperiment e
  WHERE
    e.experiment_id = 'REFI_CASHBACK2'
    AND e.timestamp >= toDateTime('2021-12-01 00:00:00')
),
__metric as (
  -- Metric (Initial Offer Viewed)
  SELECT
    i.anonymous_id as user_id,
    1 as value,
    m.timestamp as conversion_start,
    m.timestamp as conversion_end
  FROM
    (
      SELECT
        distinct dec_user_id as user_id,
        created_at as timestamp
      from
        events.track_all
      where
        event_category = 'Refi'
        and event_action = 'Initial Offers'
        and event_label = 'Viewed'
    ) m
    JOIN __identities i ON (i.user_id = m.user_id)
  WHERE
    m.timestamp >= toDateTime('2021-12-01 00:00:00')
),
__distinctUsers as (
  -- One row per user/dimension
  SELECT
    e.user_id,
    'All' as dimension,
    if(
      count(distinct e.variation) > 1,
      '__multiple__',
      max(e.variation)
    ) as variation,
    MIN(e.conversion_start) as conversion_start,
    MIN(e.conversion_end) as conversion_end
  FROM
    __experiment e
  GROUP BY
    dimension,
    e.user_id
),
__userMetric as (
  -- Add in the aggregate metric value for each user
  SELECT
    d.variation,
    d.dimension,
    1 as value
  FROM
    __distinctUsers d
    JOIN __metric m ON (m.user_id = d.user_id)
  WHERE
    m.conversion_start >= d.conversion_start
    AND m.conversion_start <= d.conversion_end
  GROUP BY
    variation,
    dimension,
    d.user_id
),
__overallUsers as (
  -- Number of users in each variation
  SELECT
    variation,
    dimension,
    COUNT(*) as users
  FROM
    __distinctUsers
  GROUP BY
    variation,
    dimension
),
__stats as (
  -- Sum all user metrics together to get a total per variation/dimension
  SELECT
    variation,
    dimension,
    COUNT(*) as count,
    AVG(value) as mean,
    stddevSamp(value) as stddev
  FROM
    __userMetric
  GROUP BY
    variation,
    dimension
)
SELECT
  s.variation,
  s.dimension,
  s.count,
  s.mean,
  s.stddev,
  u.users
FROM
  __stats s
  JOIN __overallUsers u ON (
    s.variation = u.variation
    AND s.dimension = u.dimension
  )
click to minimize
400: Code: 53, e.displ

Nullable(Int64) vs String