gistfile1.txt

至兩千年後的你

WITH
  main_query_before_export AS (
  SELECT
    COUNT(*) AS `value`,
    TIMESTAMP_TRUNC(events_c_created_at, DAY, "Asia/Taipei") AS `created_at`,
    0 AS `measure_index`,
    `users_gender` AS `users_gender`
  FROM (
    SELECT
      c_cdp_user_id AS `events_c_cdp_user_id`,
      c_event_name AS `events_c_event_name`,
      c_created_at AS `events_c_created_at`
    FROM
      `ika-890079-1`.`ika_890079_1_cdp_core_data`.`events` AS `events`
    UNION ALL (
      SELECT
        c_cdp_user_id AS `events_c_cdp_user_id`,
        c_event_name AS `events_c_event_name`,
        c_created_at AS `events_c_created_at`
      FROM
        `ika-890079-1`.`ika_890079_1_cdp_offline_data`.`offline_events`)) AS `events`
  LEFT JOIN (
    SELECT
      *
    FROM (
      SELECT
        c_cdp_user_id AS `users_c_cdp_user_id`,
        gender AS `users_gender`
      FROM
        `ika-890079-1`.`ika_890079_1_cdp_offline_data`.`cdp_user_profiles` AS `users`) AS `users`) AS `users`
  ON
    (`events_c_cdp_user_id` = `users_c_cdp_user_id`)
  WHERE
    ((`events_c_event_name` = 'first_visit')
      AND (`events_c_created_at` BETWEEN '2022-12-31 16:00:00+00:00'
        AND '2023-01-04 15:59:59.999+00:00'))
  GROUP BY
    TIMESTAMP_TRUNC(events_c_created_at, DAY, "Asia/Taipei"),
    `users_gender`
  UNION ALL (
    SELECT
      COUNT(*) AS `value`,
      TIMESTAMP_TRUNC(events_c_created_at, DAY, "Asia/Taipei") AS `created_at`,
      1 AS `measure_index`,
      `users_gender` AS `users_gender`
    FROM (
      SELECT
        c_cdp_user_id AS `events_c_cdp_user_id`,
        c_event_name AS `events_c_event_name`,
        c_created_at AS `events_c_created_at`
      FROM
        `ika-890079-1`.`ika_890079_1_cdp_core_data`.`events` AS `events`
      UNION ALL (
        SELECT
          c_cdp_user_id AS `events_c_cdp_user_id`,
          c_event_name AS `events_c_event_name`,
          c_created_at AS `events_c_created_at`
        FROM
          `ika-890079-1`.`ika_890079_1_cdp_offline_data`.`offline_events`)) AS `events`
    LEFT JOIN (
      SELECT
        *
      FROM (
        SELECT
          c_cdp_user_id AS `users_c_cdp_user_id`,
          gender AS `users_gender`
        FROM
          `ika-890079-1`.`ika_890079_1_cdp_offline_data`.`cdp_user_profiles` AS `users`) AS `users`) AS `users`
    ON
      (`events_c_cdp_user_id` = `users_c_cdp_user_id`)
    WHERE
      ((`events_c_event_name` = 'user_engagement')
        AND (`events_c_created_at` BETWEEN '2022-12-31 16:00:00+00:00'
          AND '2023-01-04 15:59:59.999+00:00'))
    GROUP BY
      TIMESTAMP_TRUNC(events_c_created_at, DAY, "Asia/Taipei"),
      `users_gender`))
SELECT
  FORMAT_TIMESTAMP("%FT%T%Ez", `main_query_before_export`.`created_at`, "Asia/Taipei") AS `時間`,
  `main_query_before_export`.`users_gender` AS `group0`,
  CASE
    WHEN COUNTIF(measure_index = 0) = 1 THEN IFNULL(ARRAY_AGG(value ORDER BY measure_index)[SAFE_OFFSET(COUNTIF(measure_index < 0))], 0)
  ELSE
  0
END
  AS `measure0`,
  CASE
    WHEN COUNTIF(measure_index = 1) = 1 THEN IFNULL(ARRAY_AGG(value ORDER BY measure_index)[SAFE_OFFSET(COUNTIF(measure_index < 1))], 0)
  ELSE
  0
END
  AS `measure1`
FROM
  `main_query_before_export`
GROUP BY
  `時間`,
  `group0`
ORDER BY
  `時間` ASC,
  `group0` ASC