#standardSQL
WITH

-- 集計範囲 (この例では昨日分; 変更可)
event_range AS (
  SELECT
    DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AS from_date,
    DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AS to_date
),

-- 範囲の全イベント
all_events AS (
  SELECT 
    event_date,
    platform,
    user_pseudo_id,
    event_name
  FROM 
   `analytics_<あなたの Analytics の ID>.events_*`,
   event_range
WHERE
    _TABLE_SUFFIX
        BETWEEN
            FORMAT_DATE("%Y%m%d", event_range.from_date) 
        AND
            FORMAT_DATE("%Y%m%d", event_range.to_date)
), 

-- スクリーンビュー
screen_views AS (
SELECT
  event_date,
  platform,
  COUNT(*) screen_views
FROM
  all_events
WHERE
  event_name = 'screen_view'
GROUP BY
  event_date,
  platform
),

-- ユニークユーザー
unique_users AS (
SELECT
  event_date,
  platform,
  COUNT(DISTINCT user_pseudo_id) unique_users
FROM
  all_events
WHERE
  event_name = 'user_engagement'
GROUP BY
  event_date,
  platform
),

-- 新規ユーザー
new_users AS (
SELECT
  event_date,
  platform,
  COUNT(DISTINCT user_pseudo_id) new_users
FROM
  all_events
WHERE
  event_name = 'first_open'
GROUP BY
  event_date,
  platform
),

-- セッション
sessions AS (
SELECT
  event_date,
  platform,
  COUNT(*) sessions
FROM
  all_events
WHERE
  event_name = 'session_start'
GROUP BY
  event_date,
  platform
)

-- 結合
SELECT
  sv.event_date,
  sv.platform,
  sv.screen_views,
  uu.unique_users,
  nu.new_users,
  s.sessions
FROM
  screen_views sv
  INNER JOIN unique_users uu
        ON   sv.event_date = uu.event_date
        AND  sv.platform   = uu.platform
  INNER JOIN new_users nu
        ON   sv.event_date = nu.event_date
        AND  sv.platform   = nu.platform
  INNER JOIN sessions s
        ON   sv.event_date = s.event_date
        AND  sv.platform = s.platform
;