-- Fact Table: Events (Guilds)
WITH
  __rawExperiment AS (
    SELECT
      *,
      attributes.guild_id:: Nullable(String) AS guild_id
    FROM
      experiment_views
    WHERE
      experiment_id LIKE 'premium-promo-button-label'
      AND timestamp BETWEEN '2026-02-03 02:10:00' AND '2026-07-10 03:43:56'
  ),
  __experimentExposures AS (
    -- Viewed Experiment
    SELECT
      e.guild_id as guild_id,
      toString(e.variation_id) as variation,
      e.timestamp as timestamp
    FROM
      __rawExperiment e
    WHERE
      e.experiment_id = 'premium-promo-button-label'
      AND e.timestamp >= toDateTime('2026-02-03 02:10:00', 'UTC')
      AND e.timestamp <= toDateTime('2026-07-10 03:43:56', 'UTC')
  ),
  __experimentUnits AS (
    -- One row per user
    SELECT
      e.guild_id AS guild_id,
      if(
        count(distinct e.variation) > 1,
        '__multiple__',
        max(e.variation)
      ) AS variation,
      MIN(e.timestamp) AS first_exposure_timestamp
    FROM
      __experimentExposures e
    GROUP BY
      e.guild_id
  ),
  __distinctUsers AS (
    SELECT
      guild_id,
      variation,
      first_exposure_timestamp AS timestamp,
      dateTrunc('day', first_exposure_timestamp) AS first_exposure_date
    FROM
      __experimentUnits
  ),
  __factTable as (
    -- Fact Table (Events (Guilds))
    SELECT
      guild_id as guild_id,
      m.timestamp as timestamp,
      -- Premium Promo Click
      CASE
        WHEN ((event_name = 'Premium Promo Click')) THEN 1
        ELSE NULL
      END as m0_value
    FROM
      (
        SELECT
          *,
          guild_id
        FROM
          events
        WHERE
          timestamp BETWEEN '2026-02-03 02:10:00' AND '2026-07-10 03:43:56'
      ) m
    WHERE
      m.timestamp >= toDateTime('2026-02-03 02:10:00', 'UTC')
      AND m.timestamp <= toDateTime('2026-07-10 03:43:56', 'UTC')
      AND (event_name = 'Premium Promo Click')
  ),
  __userMetricJoin as (
    SELECT
      d.variation AS variation,
      d.timestamp AS timestamp,
      d.guild_id AS guild_id,
      if(
        m.timestamp >= d.timestamp
        AND m.timestamp <= toDateTime('2026-07-10 03:43:56', 'UTC'),
        m.m0_value,
        NULL
      ) as m0_value
    FROM
      __distinctUsers d
      LEFT JOIN __factTable m ON (m.guild_id = d.guild_id)
  ),
  __userMetricAgg as (
    -- Add in the aggregate metric value for each user
    SELECT
      umj.variation,
      umj.guild_id,
      COALESCE(MAX(umj.m0_value), 0) AS m0_value
    FROM
      __userMetricJoin umj
    GROUP BY
      umj.variation,
      umj.guild_id
  )
  -- One row per variation/dimension with aggregations
SELECT
  m.variation AS variation,
  COUNT(*) AS users,
  toString('fact__19g6mml5yl3lg') as m0_id,
  SUM(COALESCE(m.m0_value, 0)) AS m0_main_sum,
  SUM(POWER(COALESCE(m.m0_value, 0), 2)) AS m0_main_sum_squares
FROM
  __userMetricAgg m
GROUP BY
  m.variation