-- Test New Checkout UI Component (binomial)
WITH
  __rawExperiment as (
    SELECT
      user_id,
      received_at as timestamp,
      experiment_id,
      variation_id,
      context_campaign_source as source,
      context_campaign_medium as medium,
      (
        CASE
          WHEN context_user_agent LIKE '%Mobile%' THEN 'Mobile'
          ELSE 'Tablet/Desktop'
        END
      ) as device,
      (
        CASE
          WHEN context_user_agent LIKE '% Firefox%' THEN 'Firefox'
          WHEN context_user_agent LIKE '% OPR%' THEN 'Opera'
          WHEN context_user_agent LIKE '% Edg%' THEN ' Edge'
          WHEN context_user_agent LIKE '% Chrome%' THEN 'Chrome'
          WHEN context_user_agent LIKE '% Safari%' THEN 'Safari'
          ELSE 'Other'
        END
      ) as browser
    FROM
      staging_react_web.experiment_viewed
    WHERE
      user_id is not null
  ),
  __experiment as ( -- Viewed Experiment
    SELECT
      e.user_id as user_id,
      cast(e.variation_id as varchar) as variation,
      e.timestamp as timestamp,
      e.timestamp as conversion_start,
      e.timestamp + INTERVAL '72 hours' as conversion_end
    FROM
      __rawExperiment e
    WHERE
      e.experiment_id = 'web-checkout-new-address'
      AND e.timestamp >= '2023-04-19 11:07:00'
  ),
  __metric as ( -- Metric (Test New Checkout UI Component)
    SELECT
      user_id as user_id,
      1 as value,
      m.timestamp as timestamp,
      m.timestamp as conversion_start,
      m.timestamp as conversion_end
    FROM
      (
        SELECT
          user_id,
          anonymous_id,
          received_at as timestamp
        FROM
          staging_react_web.test_new_checkout_ui_component
      ) m
    WHERE
      m.timestamp >= '2023-04-19 11:07:00'
  ),
  __distinctUsers as (
    -- One row per user
    SELECT
      e.user_id as user_id,
      cast('All' as varchar) as dimension,
      (
        CASE
          WHEN count(distinct e.variation) > 1 THEN '__multiple__'
          ELSE max(e.variation)
        END
      ) as variation,
      MIN(e.conversion_start) as conversion_start,
      MIN(e.conversion_end) as conversion_end
    FROM
      __experiment e
    GROUP BY
      e.user_id
  ),
  __userMetric as (
    -- Add in the aggregate metric value for each user
    SELECT
      d.variation,
      d.dimension,
      d.user_id,
      MAX(1) as value
    FROM
      __distinctUsers d
      JOIN __metric m ON (m.user_id = d.user_id)
    WHERE
      m.timestamp >= d.conversion_start
      AND m.timestamp <= d.conversion_end
    GROUP BY
      d.variation,
      d.dimension,
      d.user_id
  ),
  __stats AS (
    -- One row per variation/dimension with aggregations
    SELECT
      m.variation,
      m.dimension,
      COUNT(*) AS count,
      SUM(COALESCE(m.value, 0)) AS main_sum,
      SUM(POWER(COALESCE(m.value, 0), 2)) AS main_sum_squares
    FROM
      __userMetric m
    GROUP BY
      m.variation,
      m.dimension
  ),
  __overallUsers as (
    -- Number of users in each variation/dimension
    SELECT
      variation,
      dimension,
      COUNT(*) as users
    FROM
      __distinctUsers
    GROUP BY
      variation,
      dimension
  )
SELECT
  u.variation,
  u.dimension,
  u.users AS users,
  'mean' as statistic_type,
  'binomial' as main_metric_type,
  COALESCE(s.main_sum, 0) AS main_sum,
  COALESCE(s.main_sum_squares, 0) AS main_sum_squares
FROM
  __overallUsers u
  LEFT JOIN __stats s ON (
    u.variation = s.variation
    AND u.dimension = s.dimension
  )

const gb = new GrowthBook({
  apiHost: "<https://cdn.growthbook.io>",
  clientKey: "sdk-abc123",
  trackingCallback: (experiment, result) => {
    // Example using Segment
    analytics.track("Experiment Viewed", {
      experimentId: experiment.key,
      variationId: result.key,
    });
  },
});