formatted_example.sql

DECLARE four_twenty DEFAULT DATE('2020-04-20');

WITH per_build_client_day AS (
  SELECT
    PARSE_DATETIME("%Y%m%d%H%M%S", application.build_id) AS build_id,
    client_id,
    `moz-fx-data-shared-prod`.udf.histogram_normalize(
      `moz-fx-data-shared-prod`.udf.histogram_merge(
        ARRAY_AGG(
          `moz-fx-data-shared-prod`.udf.json_extract_histogram(
            payload.histograms.FX_TAB_SWITCH_SPINNER_VISIBLE_MS
          )
        )
      )
    ) AS tab_switch_visible_ms
  FROM
    `moz-fx-data-shared-prod`.telemetry.main
  WHERE
    application.channel = 'nightly'
    AND normalized_os = 'Windows'
    AND application.build_id > FORMAT_DATE("%Y%m%d", DATE_SUB(four_twenty, INTERVAL 14 DAY))
    AND application.build_id <= FORMAT_DATE("%Y%m%d", four_twenty)
    AND DATE(submission_timestamp) >= DATE_SUB(four_twenty, INTERVAL 14 DAY)
    AND DATE(submission_timestamp) <= four_twenty
  GROUP BY
    build_id,
    client_id
),
merged_histograms AS (
  SELECT
    build_id,
    KEY,
    SUM(value) AS value,
  FROM
    per_build_client_day,
    UNNEST(per_build_client_day.tab_switch_visible_ms.VALUES)
  GROUP BY
    KEY,
    build_id
),
as_struct AS (
  SELECT
    build_id,
    STRUCT(ARRAY_AGG(STRUCT(KEY, value)) AS VALUES) AS spinner_visible_long_ms
  FROM
    merged_histograms
  GROUP BY
    build_id
),
percentiles AS (
  SELECT
    build_id,
    `moz-fx-data-shared-prod`.udf.histogram_percentiles(
      spinner_visible_long_ms,
      [.05, .25, .5, .75, .95]
    ) AS percentile_nested
  FROM
    as_struct
)
SELECT
  build_id,
  percentile,
  value
FROM
  percentiles
CROSS JOIN
  UNNEST(percentiles.percentile_nested);