SELECT
  record_schema,
  data_channel,
  record_count
FROM (
        SELECT
          'amdp' AS record_schema,
          'AQFERCONVERSIONS' AS data_channel,
          COUNT(conv) AS record_count,
          event_date
        FROM
          aqfer.collation_hourly_v6_1 AS chv61,
          UNNEST(chv61.conversion.list) AS conv
        WHERE
          imp.element.has_click = FALSE
          AND ARRAY_LENGTH(conv.element.conversions.list) = 0
        GROUP BY
          event_date
  UNION ALL
  SELECT
    'amdp' AS record_schema,
    'AQFERVIEWS' AS data_channel,
    COUNT(VIEW) AS record_count,
    event_date
  FROM
    aqfer.collation_hourly_v6_1 AS chv61,
    UNNEST(chv61.views.list) AS VIEW
  WHERE
    view.element.has_interaction = FALSE
  GROUP BY
    event_date
  UNION ALL
  SELECT
    'amdp' AS record_schema,
    'AQFERINTERACTIONS' AS data_channel,
    SUM(ARRAY_LENGTH(view.element.interaction.list)) AS record_count,
    event_date
  FROM
    aqfer.collation_hourly_v6_1 AS chv61,
    UNNEST(chv61.views.list) AS VIEW
  GROUP BY
    event_date
  UNION ALL
  SELECT
    'amdp' AS record_schema,
    'AQFERIMPRESSIONS' AS data_channel,
    COUNT(imp) AS record_count,
    event_date
  FROM
    aqfer.collation_hourly_v6_1 AS chv61,
    UNNEST(chv61.impressions.list) AS imp
  WHERE
    imp.element.has_click = FALSE
    AND ARRAY_LENGTH(imp.element.engagements.list) = 0
  GROUP BY
    event_date
  UNION ALL
  SELECT
    'amdp' AS record_schema,
    'AQFERCLICKS' AS data_channel,
    SUM(ARRAY_LENGTH(imp.element.clicks.list)) AS record_count,
    event_date
  FROM
    aqfer.collation_hourly_v6_1 AS chv61,
    UNNEST(chv61.impressions.list) AS imp
  GROUP BY
    event_date
  UNION ALL
  SELECT
    'amdp' AS record_schema,
    'AQFERENGAGEMENTS' AS data_channel,
    SUM(ARRAY_LENGTH(imp.element.engagements.list)) AS record_count,
    event_date
  FROM
    aqfer.collation_hourly_v6_1 AS chv61,
    UNNEST(chv61.impressions.list) AS imp
  GROUP BY
    event_date
  UNION ALL
  SELECT
    'amdp' AS record_schema,
    'AQFERPOIVISITS' AS data_channel,
    SUM(ARRAY_LENGTH(poi_visits.list)) AS record_count,
    event_date
  FROM
    aqfer.collation_hourly_v6_1
  GROUP BY
    event_date
  UNION ALL
  SELECT
    'amdp' AS record_schema,
    'AQFEROTHEREVENTS' AS data_channel,
    SUM(ARRAY_LENGTH(other_event.list)) AS record_count,
    event_date
  FROM
    aqfer.collation_hourly_v6_1
  GROUP BY
    event_date
  UNION ALL
  SELECT
    'amdp' AS record_schema,
    'AQFERIDSYNCS' AS data_channel,
    SUM(ARRAY_LENGTH(id_syncs.list)) AS record_count,
    event_date
  FROM
    aqfer.collation_hourly_v6_1
  GROUP BY
    event_date
  UNION ALL
  SELECT
    'amdp' AS record_schema,
    'AQFERTEXTCLICKS' AS data_channel,
    SUM(ARRAY_LENGTH(text_clicks.list)) AS record_count,
    event_date
  FROM
    aqfer.collation_hourly_v6_1
  GROUP BY
    event_date )
WHERE
  record_count > 0
  AND event_date = 20240131;