-- Site-Wide - Bounce Rate Metric
WITH __pageviews as (
  SELECT
    user_id as user_id,
    user_id as anonymous_id,
    received_at as timestamp,
    path as url
  FROM
    pages
),
__users as (
  -- Users visiting specific pages
  SELECT
    p.anonymous_id as user_id,
    MIN(p.timestamp) as actual_start,
    MIN(p.timestamp) + INTERVAL '72 hours' as conversion_end,
    MIN(p.timestamp) - INTERVAL '30 minutes' as session_start
  FROM
    __pageviews p
  WHERE
    p.timestamp >= '2021-10-27 00:00:00'
    AND p.timestamp <= '2022-01-25 00:00:00'
  GROUP BY
    p.anonymous_id
),
__metric as (
  -- Metric (Bounce Rate)
  SELECT
    m.anonymous_id as user_id,
    1 as value,
    m.date_end as actual_start,
    m.date_end + INTERVAL '72 hours' as conversion_end,
    m.date_end - INTERVAL '30 minutes' as session_start
  FROM
    sessions m
  WHERE
    m.num_pages = '1'
    AND m.date_end >= '2021-10-27 02:42:21'
    AND m.date_end <= '2022-01-28 03:12:21'
),
__distinctUsers as (
  SELECT
    u.user_id,
    MIN(u.conversion_end) as conversion_end,
    MIN(u.session_start) as session_start,
    MIN(u.actual_start) as actual_start
  FROM
    __users u
  GROUP BY
    u.user_id
),
__userMetric as (
  -- Add in the aggregate metric value for each user
  SELECT
    1 as value
  FROM
    __distinctUsers d
    JOIN __metric m ON (m.user_id = d.user_id)
  WHERE
    m.actual_start >= d.session_start
    AND m.actual_start <= d.conversion_end
  GROUP BY
    d.user_id
),
__overallUsers as (
  -- Number of users overall
  SELECT
    COUNT(*) as users
  FROM
    __distinctUsers
),
__overall as (
  SELECT
    COUNT(*) as count,
    AVG(value:: float) as mean,
    STDDEV(value) as stddev
  from
    __userMetric
),
__userMetricDates as (
  -- Add in the aggregate metric value for each user
  SELECT
    date_trunc('day', d.actual_start) as date,
    1 as value
  FROM
    __distinctUsers d
    JOIN __metric m ON (m.user_id = d.user_id)
  WHERE
    m.actual_start >= d.session_start
    AND m.actual_start <= d.conversion_end
  GROUP BY
    date_trunc('day', d.actual_start),
    d.user_id
),
__usersByDate as (
  -- Number of users by date
  SELECT
    date_trunc('day', actual_start) as date,
    COUNT(*) as users
  FROM
    __distinctUsers
  GROUP BY
    date_trunc('day', actual_start)
),
__byDateOverall as (
  SELECT
    date,
    COUNT(*) as count,
    AVG(value:: float) as mean,
    STDDEV(value) as stddev
  FROM
    __userMetricDates d
  GROUP BY
    date
)
SELECT
null as
  date,
  o.*,
  u.users
FROM
  __overall o
  JOIN __overallUsers u ON (1 = 1)
UNION ALL
SELECT
  o.*,
  u.users
FROM
  __byDateOverall o
  JOIN __usersByDate u ON (o.date = u.date)
ORDER BY
  date ASC

sessions

SELECT
    user_id,
    user_pseudo_id AS anonymous_id,
    MIN(TIMESTAMP_MICROS(event_timestamp)) as timestamp,
    concat(user_pseudo_id,(select value.int_value FROM unnest(event_params) WHERE key = 'ga_session_id')) as session_id,
    countif(event_name = 'page_view') as views,
FROM
  *** your table ***
WHERE
    ((_TABLE_SUFFIX BETWEEN '{{date startDateISO "yyyyMMdd"}}' AND '{{date endDateISO "yyyyMMdd"}}')
    OR (_TABLE_SUFFIX BETWEEN 'intraday_{{date startDateISO "yyyyMMdd"}}' AND 'intraday_{{date endDateISO "yyyyMMdd"}}'))
group by
  user_id,
  user_pseudo_id,
  session_id
HAVING views = 1