CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20240401');  -- 開始日
CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20240930');    -- 終了日
CREATE TEMP FUNCTION parameter_name() RETURNS STRING AS ('session_engaged');  -- パラメータ名
--CREATE TEMP FUNCTION event_name_list() RETURNS ARRAY<STRING> AS (['session_start']); -- 単一のイベントを検証
CREATE TEMP FUNCTION event_name_list() RETURNS ARRAY<STRING> AS (['session_start', 'page_view', 'click', 'first_visit', 'scroll', 'user_engagement']);  -- 複数イベント名を検証
--CREATE TEMP FUNCTION event_name_list() RETURNS ARRAY<STRING> AS (['']);  -- 空欄指定で全イベント抽出

WITH
  parameter_types AS (
    SELECT
      event_name,
      param.key AS parameter_name,
      param.value.int_value AS int_value,
      param.value.float_value AS float_value,
      param.value.double_value AS double_value,
      param.value.string_value AS string_value,
      CASE 
        WHEN param.value.int_value IS NOT NULL THEN 'int_value'
        WHEN param.value.float_value IS NOT NULL THEN 'float_value'
        WHEN param.value.double_value IS NOT NULL THEN 'double_value'
        WHEN param.value.string_value IS NOT NULL THEN 'string_value'
        ELSE 'unknown'
      END AS parameter_type,
      COUNT(*) AS parameter_count
    FROM
      `<project>.<dataset>.events_*`,
      UNNEST(event_params) AS param
    WHERE
      _TABLE_SUFFIX BETWEEN date_from() AND date_to()
      AND (ARRAY_LENGTH(event_name_list()) = 1 AND event_name_list()[OFFSET(0)] = '' OR event_name IN UNNEST(event_name_list()))
    GROUP BY
      event_name,
      parameter_name,
      int_value,
      float_value,
      double_value,
      string_value,
      parameter_type
)

SELECT
  event_name,
  parameter_name,
  parameter_type, 
  int_value,
  float_value,
  double_value,
  string_value,
  parameter_count
FROM
  parameter_types
WHERE
  parameter_name = parameter_name()
ORDER BY
  event_name,
  parameter_name;

CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20240401');

WITH
  CNT_EngagementSession AS (
    SELECT
      ymd,
      COUNT(ssid) AS _eng_ss
    FROM (
      SELECT
        ymd,
        CONCAT(user_pseudo_id, '-', CAST(ga_session_id AS STRING)) AS ssid
      FROM (
        SELECT
          PARSE_DATE("%Y%m%d", event_date) AS ymd,
          user_pseudo_id,
          (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id
        FROM
          `<project>.<dataset>.events_*`  
        WHERE
            _TABLE_SUFFIX BETWEEN date_from() AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
          AND
            (SELECT COALESCE(value.string_value, SAFE_CAST(value.int_value AS STRING)) FROM UNNEST(event_params) WHERE key = 'session_engaged') = '1'
          AND
            event_name = 'session_start'
      )
      GROUP BY
        ymd,
        ssid
    )
    GROUP BY
      ymd
  )

SELECT
  *
FROM
  CNT_EngagementSession
ORDER BY
  ymd ASC