count

users

-- Gross TTV (revenue)
WITH
  __rawExperiment as (
    SELECT
      id,
      created_at as timestamp,
      experiment as experiment_id,
      variation_id as variation_id
    FROM
      experiments.assignments
  ),
  __experiment as ( -- Viewed Experiment
    SELECT
      e.id as id,
      cast(e.variation_id as varchar) as variation,
      e.timestamp as timestamp,
      e.timestamp as conversion_start,
      e.timestamp + INTERVAL '72' hour as conversion_end
    FROM
      __rawExperiment e
    WHERE
      e.experiment_id = 'my_experiment'
      AND e.timestamp >= from_iso8601_timestamp('2023-11-10T17:30:00.000Z')
  ),
  __metric as ( -- Metric (Gross TTV)
    SELECT
      id as id,
      m.value as value,
      m.timestamp as timestamp,
      m.timestamp as conversion_start,
      m.timestamp as conversion_end
    FROM
      (
        SELECT
          id,
          timestamp,
          itinerary_sell_rate_total.usd_amount AS value
        FROM
          experiments.master
      ) m
    WHERE
      m.timestamp >= from_iso8601_timestamp('2023-11-10T17:30:00.000Z')
  ),
  __distinctUsers as (
    -- One row per user/dimension
    SELECT
      e.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.id
  ),
  __userMetric as (
    -- Add in the aggregate metric value for each user
    SELECT
      d.variation,
      d.dimension,
      d.id,
      LEAST(3500, SUM(value)) as value
    FROM
      __distinctUsers d
      JOIN __metric m ON (m.id = d.id)
    WHERE
      m.timestamp >= d.conversion_start
      AND m.timestamp <= d.conversion_end
    GROUP BY
      variation,
      dimension,
      d.id
  ),
  __overallUsers as (
    -- Number of users in each variation
    SELECT
      variation,
      dimension,
      1.0 * COUNT(*) as users
    FROM
      __distinctUsers
    GROUP BY
      variation,
      dimension
  ),
  __stats as (
    -- One row per variation/dimension with aggregations
    SELECT
      m.variation,
      m.dimension,
      1.0 * COUNT(*) as count,
      AVG(1.0 * coalesce(m.value, 0)) as m_mean,
      VAR_SAMP(coalesce(m.value, 0)) as m_var,
      1.0 * sum(m.value) as m_sum
    FROM
      __userMetric m
    GROUP BY
      m.variation,
      m.dimension
  ),
  __overall as (
    SELECT
      u.variation,
      u.dimension,
      u.users as count,
      s.m_mean * s.count / u.users as mean,
      (
        CASE
          WHEN u.users > 1 THEN (s.count -1) * s.m_var / (u.users -1) + s.count * (u.users - s.count) * power(s.m_mean, 2) / (u.users * (u.users -1))
          ELSE 0
        END
      ) as variance,
      u.users as users
    FROM
      __overallUsers u
      LEFT JOIN __stats s ON (
        s.variation = u.variation
        AND s.dimension = u.dimension
      )
  )
SELECT
  variation,
  dimension,
  count,
  mean,
  (
    CASE
      WHEN variance > 0 THEN sqrt(variance)
      ELSE 0
    END
  ) as stddev,
  users
FROM
  __overall

parseFloat