ga026.sql

SELECT
  PARSE_DATE('%Y%m%d',
    event_date) AS date,
  stream_id,
  -- note, stream_id is available for web streams as well
  app_info.id AS app_id,
  app_info.version AS app_version,
  platform AS app_platform,
  device.mobile_brand_name AS device_brand,
  device.mobile_model_name AS device_model,
  -- to count our events, we will use our event_name, and count USERS concatenated with a timestamp
  -- this gives us each event per user, or all events.
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'first_open' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING))
  END
    ) AS first_open,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'app_update' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING))
  END
    ) AS app_update,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'app_remove' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING))
  END
    ) AS app_remove,
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'post_score' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING))
  END
    ) AS post_score
FROM
  `firebase-public-project.analytics_153293282.events_*` -- modify to your project
WHERE
  _table_suffix = '20181003'
GROUP BY
  1,
  2,
  3,
  4,
  5,
  6,
  7