relation "identifies" does not exist

-- % of Users Finalized Selection Step [Step 2] (7d) (binomial)
WITH
  __identities0 as (
    SELECT
      anonymous_id,
      user_id
    FROM
      (
        SELECT
          user_id,
          anonymous_id
        FROM
          identifies
      ) i
    GROUP BY
      anonymous_id,
      user_id
  ),
  __rawExperiment AS (
    SELECT
      anonymous_id,
      received_at as timestamp,
      experiment_key as 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
      groover_frontend_prod.growthbook_experiment_viewed
    WHERE
      anonymous_id is not null
  ),
  __experimentExposures AS (
    -- Viewed Experiment
    SELECT
      e.anonymous_id as anonymous_id,
      cast(e.variation_id as varchar) as variation,
      e.timestamp as timestamp
    FROM
      __rawExperiment e
    WHERE
      e.experiment_id = 'ratings-social-proof'
      AND e.timestamp >= '2023-12-14 17:58:44'
      AND e.timestamp <= '2023-12-15 00:09:29'
  ),
  __experimentUnits AS (
    -- One row per user
    SELECT
      e.anonymous_id AS anonymous_id,
      (
        CASE
          WHEN count(distinct e.variation) > 1 THEN '__multiple__'
          ELSE max(e.variation)
        END
      ) AS variation,
      MIN(e.timestamp) AS first_exposure_timestamp
    FROM
      __experimentExposures e
    GROUP BY
      e.anonymous_id
  ),
  __distinctUsers AS (
    SELECT
      anonymous_id,
      cast('All' as varchar) AS dimension,
      variation,
      first_exposure_timestamp AS timestamp,
      date_trunc('day', first_exposure_timestamp) AS first_exposure_date
    FROM
      __experimentUnits
  ),
  __metric as ( -- Metric (% of Users Finalized Selection Step [Step 2] (7d))
    SELECT
      i.anonymous_id as anonymous_id,
      1 as value,
      m.timestamp as timestamp
    FROM
      (
        SELECT DISTINCT
          user_id,
          received_at as timestamp
        FROM
          groover_frontend_prod.campaign_step_2_curators_selection_validated
      ) m
      JOIN __identities0 i ON (i.user_id = m.user_id)
    WHERE
      m.timestamp >= '2023-12-14 17:58:44'
      AND m.timestamp <= '2023-12-22 00:09:29'
  ),
  __userMetricJoin as (
    SELECT
      d.variation AS variation,
      d.dimension AS dimension,
      d.anonymous_id AS anonymous_id,
      (
        CASE
          WHEN m.timestamp >= d.timestamp
          AND m.timestamp <= d.timestamp + INTERVAL '168 hours' THEN m.value
          ELSE NULL
        END
      ) as value
    FROM
      __distinctUsers d
      LEFT JOIN __metric m ON (m.anonymous_id = d.anonymous_id)
  ),
  __userMetricAgg as (
    -- Add in the aggregate metric value for each user
    SELECT
      variation,
      dimension,
      anonymous_id,
      MAX(COALESCE(value, 0)) as value
    FROM
      __userMetricJoin
    GROUP BY
      variation,
      dimension,
      anonymous_id
  )
  -- One row per variation/dimension with aggregations
SELECT
  m.variation AS variation,
  m.dimension AS dimension,
  COUNT(*) AS users,
  'mean' as statistic_type,
  'binomial' as main_metric_type,
  SUM(COALESCE(m.value, 0)) AS main_sum,
  SUM(POWER(COALESCE(m.value, 0), 2)) AS main_sum_squares
FROM
  __userMetricAgg m
GROUP BY
  m.variation,
  m.dimension

anonymous_id

user_id

anonymous_id

SELECT
          user_id,
          anonymous_id
        FROM
          identifies

identifies

Join Tables

identifies

user_id

id

user_id

id

user_id