backfill/2023-09-26-initialize-clients_first_seen_v2/telemetry_derived_clients_first_seen_v2_query.sql [11:51]:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
  SELECT
    client_id AS client_id,
    sample_id AS sample_id,
    MIN(submission_timestamp) AS first_seen_timestamp,
    ARRAY_AGG(DATE(submission_timestamp) ORDER BY submission_timestamp ASC) AS all_dates,
    ARRAY_AGG(application.architecture RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS architecture,
    ARRAY_AGG(environment.build.build_id RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS app_build_id,
    ARRAY_AGG(normalized_app_name RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS app_name,
    ARRAY_AGG(environment.settings.locale RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS locale,
    ARRAY_AGG(application.platform_version RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS platform_version,
    ARRAY_AGG(application.vendor RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS vendor,
    ARRAY_AGG(application.version RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS app_version,
    ARRAY_AGG(application.xpcom_abi RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS xpcom_abi,
    ARRAY_AGG(document_id RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS document_id,
    ARRAY_AGG(environment.partner.distribution_id RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS distribution_id,
    ARRAY_AGG(environment.partner.distribution_version RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS partner_distribution_version,
    ARRAY_AGG(environment.partner.distributor RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS partner_distributor,
    ARRAY_AGG(environment.partner.distributor_channel RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS partner_distributor_channel,
    ARRAY_AGG(environment.partner.partner_id RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS partner_id,
    ARRAY_AGG(environment.settings.attribution.campaign RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS attribution_campaign,
    ARRAY_AGG(environment.settings.attribution.content RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS attribution_content,
    ARRAY_AGG(environment.settings.attribution.experiment RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS attribution_experiment,
    ARRAY_AGG(environment.settings.attribution.medium RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS attribution_medium,
    ARRAY_AGG(environment.settings.attribution.source RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS attribution_source,
    ARRAY_AGG(environment.settings.attribution.ua RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS attribution_ua,
    ARRAY_AGG(environment.settings.default_search_engine_data.load_path RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS engine_data_load_path,
    ARRAY_AGG(environment.settings.default_search_engine_data.name RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS engine_data_name,
    ARRAY_AGG(environment.settings.default_search_engine_data.origin RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS engine_data_origin,
    ARRAY_AGG(environment.settings.default_search_engine_data.submission_url RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS engine_data_submission_url,
    ARRAY_AGG(environment.system.apple_model_id RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS apple_model_id,
    ARRAY_AGG(metadata.geo.city RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS city,
    ARRAY_AGG(metadata.geo.db_version RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS db_version,
    ARRAY_AGG(metadata.geo.subdivision1 RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS subdivision1,
    ARRAY_AGG(normalized_channel RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS normalized_channel,
    ARRAY_AGG(normalized_country_code RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS country,
    ARRAY_AGG(normalized_os RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS normalized_os,
    ARRAY_AGG(normalized_os_version RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS normalized_os_version,
    ARRAY_AGG(payload.processes.parent.scalars.startup_profile_selection_reason RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS startup_profile_selection_reason,
    ARRAY_AGG(environment.settings.attribution.dltoken RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS attribution_dltoken,
    ARRAY_AGG(environment.settings.attribution.dlsource RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS attribution_dlsource,
  FROM
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -



backfill/2023-09-26-initialize-clients_first_seen_v2/telemetry_derived_clients_first_seen_v2_query.sql [67:107]:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
  SELECT
    client_id AS client_id,
    sample_id AS sample_id,
    MIN(submission_timestamp) AS first_seen_timestamp,
    ARRAY_AGG(DATE(submission_timestamp) ORDER BY submission_timestamp ASC) AS all_dates,
    ARRAY_AGG(application.architecture RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS architecture,
    ARRAY_AGG(environment.build.build_id RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS app_build_id,
    ARRAY_AGG(normalized_app_name RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS app_name,
    ARRAY_AGG(environment.settings.locale RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS locale,
    ARRAY_AGG(application.platform_version RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS platform_version,
    ARRAY_AGG(application.vendor RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS vendor,
    ARRAY_AGG(application.version RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS app_version,
    ARRAY_AGG(application.xpcom_abi RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS xpcom_abi,
    ARRAY_AGG(document_id RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS document_id,
    ARRAY_AGG(environment.partner.distribution_id RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS distribution_id,
    ARRAY_AGG(environment.partner.distribution_version RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS partner_distribution_version,
    ARRAY_AGG(environment.partner.distributor RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS partner_distributor,
    ARRAY_AGG(environment.partner.distributor_channel RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS partner_distributor_channel,
    ARRAY_AGG(environment.partner.partner_id RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS partner_id,
    ARRAY_AGG(environment.settings.attribution.campaign RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS attribution_campaign,
    ARRAY_AGG(environment.settings.attribution.content RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS attribution_content,
    ARRAY_AGG(environment.settings.attribution.experiment RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS attribution_experiment,
    ARRAY_AGG(environment.settings.attribution.medium RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS attribution_medium,
    ARRAY_AGG(environment.settings.attribution.source RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS attribution_source,
    ARRAY_AGG(environment.settings.attribution.ua RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS attribution_ua,
    ARRAY_AGG(environment.settings.default_search_engine_data.load_path RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS engine_data_load_path,
    ARRAY_AGG(environment.settings.default_search_engine_data.name RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS engine_data_name,
    ARRAY_AGG(environment.settings.default_search_engine_data.origin RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS engine_data_origin,
    ARRAY_AGG(environment.settings.default_search_engine_data.submission_url RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS engine_data_submission_url,
    ARRAY_AGG(environment.system.apple_model_id RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS apple_model_id,
    ARRAY_AGG(metadata.geo.city RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS city,
    ARRAY_AGG(metadata.geo.db_version RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS db_version,
    ARRAY_AGG(metadata.geo.subdivision1 RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS subdivision1,
    ARRAY_AGG(normalized_channel RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS normalized_channel,
    ARRAY_AGG(normalized_country_code RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS country,
    ARRAY_AGG(normalized_os RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS normalized_os,
    ARRAY_AGG(normalized_os_version RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS normalized_os_version,
    ARRAY_AGG(payload.processes.parent.scalars.startup_profile_selection_reason RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS startup_profile_selection_reason,
    ARRAY_AGG(environment.settings.attribution.dltoken RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS attribution_dltoken,
    ARRAY_AGG(environment.settings.attribution.dlsource RESPECT NULLS ORDER BY submission_timestamp)[SAFE_OFFSET(0)] AS attribution_dlsource,
  FROM
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -



