Firebase AnalyticsのBigQueryから、それぞれのユーザーの直近1ヶ月の最終アクティブ時刻を抽出

user_active.sql

CREATE TEMPORARY FUNCTION
  today() AS ( (
    SELECT
      CURRENT_DATE('Asia/Tokyo')) );
CREATE TEMPORARY FUNCTION
  formatDate(d DATE) AS ( (
    SELECT
      FORMAT_DATE('%Y%m%d',d)) );
SELECT
  user_id as userId,
  TIMESTAMP_MICROS(MAX(event_timestamp)) AS timestamp
FROM
  `YOUR_PROJECT.YOUR_NEW_FORMAT_DATASET.events_*`,
WHERE
  _TABLE_SUFFIX BETWEEN formatDate(DATE_ADD(today(), INTERVAL -1 MONTH))
  AND formatDate(DATE_ADD(today(), INTERVAL -1 DAY))
  AND user_id IS NOT NULL
GROUP BY
  user_id
ORDER BY
  timestamp DESC