sql/moz-fx-data-shared-prod/firefox_desktop_derived/newtab_items_daily_v1/query.sql

WITH events_unnested AS (
  SELECT
    DATE(submission_timestamp) AS submission_date,
    mozfun.norm.browser_version_info(client_info.app_display_version).major_version AS app_version,
    normalized_channel AS channel,
    metrics.string.newtab_locale AS locale,
    normalized_country_code AS country,
    timestamp AS event_timestamp,
    category AS event_category,
    name AS event_name,
    extra AS event_details,
  FROM
    `moz-fx-data-shared-prod.firefox_desktop_stable.newtab_v1`,
    UNNEST(events)
  WHERE
    DATE(submission_timestamp) = @submission_date
    AND category IN ('pocket')
    AND name IN ('impression', 'click', 'save', 'dismiss')
    AND mozfun.norm.browser_version_info(
      client_info.app_display_version
    ).major_version >= 121 -- the [Pocket team started using Glean](https://github.com/Pocket/dbt-snowflake/pull/459) from this version on. This prevents duplicates for previous releases.
),
flattened_events AS (
  SELECT
    submission_date,
    SAFE_CAST(app_version AS INT64) AS app_version,
    channel,
    locale,
    country,
    event_category,
    event_name,
    mozfun.map.get_key(event_details, 'corpus_item_id') AS corpus_item_id,
    SAFE_CAST(mozfun.map.get_key(event_details, 'position') AS INT64) AS position,
    SAFE_CAST(mozfun.map.get_key(event_details, 'is_sponsored') AS BOOLEAN) AS is_sponsored,
    SAFE_CAST(
      mozfun.map.get_key(event_details, 'is_secton_followed') AS BOOLEAN
    ) AS is_section_followed,
    mozfun.map.get_key(event_details, 'matches_selected_topic') AS matches_selected_topic,
    mozfun.map.get_key(event_details, 'newtab_visit_id') AS newtab_visit_id,
    SAFE_CAST(mozfun.map.get_key(event_details, 'received_rank') AS INT64) AS received_rank,
    mozfun.map.get_key(event_details, 'section') AS section,
    SAFE_CAST(mozfun.map.get_key(event_details, 'section_position') AS INT64) AS section_position,
    mozfun.map.get_key(event_details, 'topic') AS topic,
  FROM
    events_unnested
)
SELECT
  submission_date,
  app_version,
  channel,
  locale,
  country,
  mozfun.newtab.scheduled_surface_id_v1(country, locale) AS scheduled_surface_id,
  corpus_item_id,
  position,
  is_sponsored,
  is_section_followed,
  matches_selected_topic,
  received_rank,
  section,
  section_position,
  topic,
  COUNTIF(event_name = 'impression') AS impression_count,
  COUNTIF(event_name = 'click') AS click_count,
  COUNTIF(event_name = 'save') AS save_count,
  COUNTIF(event_name = 'dismiss') AS dismiss_count
FROM
  flattened_events
GROUP BY
  submission_date,
  app_version,
  channel,
  locale,
  country,
  scheduled_surface_id,
  corpus_item_id,
  position,
  is_sponsored,
  is_section_followed,
  matches_selected_topic,
  received_rank,
  section,
  section_position,
  topic