/***************************************************************************************************************************
Created by Václav Ráš (www.dataras.cz)
BigQuery | Unnest GA4 Query:
- Automaticky sestaví UNNESTovací sub-queries pro všechny event parametry v GA4 datasetu za posledních 7 dní
- Parametry, které mají více datových typů (např. int a string) přetypuje na jeden sjednocující (většinou string)
- Řekne vám, které event parametry mají více datových typů a jaké to jsou (může být užitečné i pro kontrolu datové čistoty)
***************************************************************************************************************************/
WITH
  parameters AS (
    SELECT
      event_param.key AS key,
      CASE 
        WHEN event_param.value.string_value IS NOT NULL THEN "string_value"
        WHEN event_param.value.int_value    IS NOT NULL THEN "int_value"
        WHEN event_param.value.double_value IS NOT NULL THEN "double_value"
      END AS data_type,
      COUNT(*) AS event_count
    FROM
      `analytics_XXXXXXXXX.events_*`, /* <--  SEM VLOŽTE GA4 DATASET KONČÍCÍ NA "events_*" */
      UNNEST(event_params) AS event_param
    WHERE
      _TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
                        AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    GROUP BY 1, 2
    ORDER BY 1),
  data_types AS (
    SELECT
      key,
      ARRAY_AGG(DISTINCT data_type IGNORE NULLS) AS data_types_agg,
      COUNT(*) AS distinct_data_types,
      SUM(event_count) AS event_count
    FROM
      parameters
    GROUP BY key
  )
SELECT
  key,
  CASE
    WHEN distinct_data_types=1 AND "string_value" IN UNNEST(data_types_agg) THEN "(SELECT value.string_value FROM UNNEST(event_params) WHERE key = '"|| key ||"') AS " || key || ","
    WHEN distinct_data_types=1 AND "int_value"    IN UNNEST(data_types_agg) THEN "(SELECT value.int_value FROM UNNEST(event_params) WHERE key = '"|| key ||"') AS " || key || ","
    WHEN distinct_data_types=1 AND "double_value" IN UNNEST(data_types_agg) THEN "(SELECT value.double_value FROM UNNEST(event_params) WHERE key = '"|| key ||"') AS " || key || ","
    WHEN distinct_data_types=2 AND "string_value" IN UNNEST(data_types_agg) AND "int_value"    IN UNNEST(data_types_agg) THEN "(SELECT COALESCE(value.string_value, CAST(value.int_value AS STRING)) FROM UNNEST(event_params) WHERE key = '"|| key ||"') AS " || key || ","
    WHEN distinct_data_types=2 AND "string_value" IN UNNEST(data_types_agg) AND "double_value" IN UNNEST(data_types_agg) THEN "(SELECT COALESCE(value.string_value, CAST(value.double_value AS STRING)) FROM UNNEST(event_params) WHERE key = '"|| key ||"') AS " || key || ","
    WHEN distinct_data_types=2 AND "int_value"    IN UNNEST(data_types_agg) AND "double_value" IN UNNEST(data_types_agg) THEN "(SELECT COALESCE(value.double_value, CAST(value.int_value AS FLOAT64)) FROM UNNEST(event_params) WHERE key = '"|| key ||"') AS " || key || ","
    ELSE "(SELECT COALESCE(value.string_value, CAST(value.int_value AS STRING), CAST(value.double_value AS STRING)) FROM UNNEST(event_params) WHERE key = '"|| key ||"') AS " || key || ","
  END AS query,
  distinct_data_types,
  data_types_agg,
  event_count
FROM
  data_types
ORDER BY key;