WITH ExperimentSessions AS (
  SELECT
    CONCAT(
      (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'),
      user_pseudo_id
    ) AS session_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'variation_id') AS variation_id,
    user_pseudo_id
  FROM
    `xyz.events_202312*`
  WHERE
    _TABLE_SUFFIX BETWEEN '13' AND '18'
    AND event_name = 'experiment_viewed'
  GROUP BY
    session_id, variation_id, user_pseudo_id
)

, counts AS (
  SELECT
    ES.session_id,
    ES.variation_id,
    COUNTIF(E.event_name = 'page_view') AS page_view_count,
    COUNTIF(E.event_name = 'ge_bort') AS ge_bort_count,
    COUNT(DISTINCT ES.user_pseudo_id) AS unique_user_count
  FROM
    ExperimentSessions ES
  JOIN
    `xyz.events_202312*` E
  ON
    ES.session_id = CONCAT(
        (SELECT value.int_value FROM UNNEST(E.event_params) WHERE key = 'ga_session_id'),
        E.user_pseudo_id
    )
  WHERE
    E._TABLE_SUFFIX BETWEEN '13' AND '18'
  GROUP BY
    ES.session_id, ES.variation_id
)

SELECT
  variation_id,
  SUM(counts.page_view_count) AS page_views,
  SUM(counts.ge_bort_count) AS ge_bort_count,
  SUM(counts.unique_user_count) AS unique_users
FROM counts
WHERE variation_id IS NOT NULL
GROUP BY 1