`-- ${metric.name} (${metric.type})
WITH
  ${idJoinSQL}
  __rawExperiment as (
    ${replaceDateVars(
      exposureQuery.query,
      phase.dateStarted,
      phase.dateEnded
    )}
  ),
  __experiment as (${this.getExperimentCTE({
    experiment,
    phase,
    baseIdType,
    conversionWindowHours:
      (activationMetric
        ? activationMetric.conversionWindowHours
        : metric.conversionWindowHours) || 0,
    conversionDelayHours:
      (activationMetric
        ? activationMetric.conversionDelayHours
        : metric.conversionDelayHours) || 0,
    experimentDimension:
      dimension?.type === "experiment" ? dimension.id : null,
  })})
  , __metric as (${this.getMetricCTE({
    metric,
    baseIdType,
    idJoinMap,
    startDate: metricStart,
    endDate: metricEnd,
  })})
  ${
    segment
      ? `, __segment as (${this.getSegmentCTE(
          segment,
          baseIdType,
          idJoinMap
        )})`
      : ""
  }
  ${
    dimension?.type === "user"
      ? `, __dimension as (${this.getDimensionCTE(
          dimension.dimension,
          baseIdType,
          idJoinMap
        )})`
      : ""
  }
  ${
    activationMetric
      ? `, __activationMetric as (${this.getMetricCTE({
          metric: activationMetric,
          conversionWindowHours:
            metric.conversionWindowHours || DEFAULT_CONVERSION_WINDOW_HOURS,
          conversionDelayHours: metric.conversionDelayHours,
          baseIdType,
          idJoinMap,
          startDate: metricStart,
          endDate: metricEnd,
        })})
        , __activatedUsers as (${this.getActivatedUsersCTE(baseIdType)})`
      : ""
  }
  , __distinctUsers as (
    -- One row per user/dimension${
      removeMultipleExposures ? "" : "/variation"
    }
    SELECT
      e.${baseIdType},
      ${
        dimension?.type === "user"
          ? "d.value"
          : dimension?.type === "experiment"
          ? "e.dimension"
          : dimension?.type === "date"
          ? this.formatDate(this.dateTrunc("e.conversion_start"))
          : activationDimension
          ? this.ifElse(
              `a.${baseIdType} IS NULL`,
              "'Not Activated'",
              "'Activated'"
            )
          : "'All'"
      } as dimension,
      ${
        removeMultipleExposures
          ? this.ifElse(
              "count(distinct e.variation) > 1",
              "'__multiple__'",
              "max(e.variation)"
            )
          : "e.variation"
      } as variation,
      MIN(${this.ifNullFallback(
        activationMetric ? "a.conversion_start" : null,
        "e.conversion_start"
      )}) as conversion_start,
      MIN(${this.ifNullFallback(
        activationMetric ? "a.conversion_end" : null,
        "e.conversion_end"
      )}) as conversion_end
    FROM
      __experiment e
      ${
        segment
          ? `JOIN __segment s ON (s.${baseIdType} = e.${baseIdType})`
          : ""
      }
      ${
        dimension?.type === "user"
          ? `JOIN __dimension d ON (d.${baseIdType} = e.${baseIdType})`
          : ""
      }
      ${
        activationMetric
          ? `
      ${activationDimension ? "LEFT " : ""}JOIN __activatedUsers a ON (
        a.${baseIdType} = e.${baseIdType}
      )`
          : ""
      }
    ${segment ? `WHERE s.date <= e.conversion_start` : ""}
    GROUP BY
      1, 2${removeMultipleExposures ? "" : ", 3"}
  )
  , __userMetric as (
    -- Add in the aggregate metric value for each user
    SELECT
      d.variation,
      d.dimension,
      ${aggregate} as value
    FROM
      __distinctUsers d
      JOIN __metric m ON (
        m.${baseIdType} = d.${baseIdType}
      )
      WHERE
        m.conversion_start >= d.conversion_start
        AND m.conversion_start <= d.conversion_end
    GROUP BY
    1,2,3
  )
  , __overallUsers as (
    -- Number of users in each variation
    SELECT
      variation,
      dimension,
      COUNT(*) as users
    FROM
      __distinctUsers
    GROUP BY
      1,
      2
  )
  , __stats as (    
    -- Sum all user metrics together to get a total per variation/dimension
    SELECT
      variation,
      dimension,
      COUNT(*) as count,
      ${this.avg("value")} as mean,
      ${this.stddev("value")} as stddev
    FROM
      __userMetric
    GROUP BY
      1,
      2
  )
SELECT
  s.variation,
  s.dimension,
  s.count,
  s.mean,
  s.stddev,
  u.users
FROM
  __stats s
  JOIN __overallUsers u ON (
    s.variation = u.variation 
    AND s.dimension = u.dimension
  )
`