gistfile1.txt

WITH raw_enrollments AS (
    SELECT
        e.client_id,
        `mozfun.map.get_key`(e.event_map_values, 'branch')
            AS branch,
        min(e.submission_date) AS enrollment_date,
        count(e.submission_date) AS num_enrollment_events
    FROM
        `moz-fx-data-shared-prod.telemetry.events` e
    WHERE
        e.event_category = 'normandy'
        AND e.event_method = 'enroll'
        AND e.submission_date
            BETWEEN '2020-10-27' AND '2020-11-10'
        AND e.event_string_value = 'bug-1671484-pref-validation-of-relpreload-performance-impact-release-82-83'
    GROUP BY e.client_id, branch
),

segmented_enrollments AS (
    SELECT
        raw_enrollments.*,
    ds_1.tcp_conn,
    merged
    FROM raw_enrollments
LEFT JOIN (
    SELECT
        e.client_id,
        COALESCE(mozfun.hist.percentiles(mozfun.hist.merge(ARRAY_AGG(tcp_conn_ms)), [0.5])[SAFE_OFFSET(0)].value, -1) AS tcp_conn,
        mozfun.hist.merge(COALESCE(ARRAY_AGG(tcp_conn_ms), [])) AS merged
    FROM raw_enrollments e
        LEFT JOIN (
          SELECT
              DATE(submission_timestamp) AS submission_date,
              client_id,
              mozfun.hist.merge(ARRAY_AGG(mozfun.hist.extract(payload.histograms.HTTP_PAGE_TCP_CONNECTION_2))) AS tcp_conn_ms,
          FROM `moz-fx-data-shared-prod.telemetry.main`
          WHERE
              DATE(submission_timestamp) BETWEEN DATE_SUB("2020-10-27", INTERVAL 8 DAY) AND "2020-11-10"
              AND normalized_channel = 'release'
          GROUP BY submission_date, client_id
        ) ds
            ON ds.client_id = e.client_id
            AND ds.submission_date BETWEEN
                DATE_ADD('2020-10-27', interval 0 day)
                AND DATE_ADD('2020-11-10', interval 0 day)
            AND ds.submission_date BETWEEN
                DATE_ADD(e.enrollment_date, interval 0 day)
                AND DATE_ADD(e.enrollment_date, interval 0 day)
    GROUP BY e.client_id
    ) ds_1 USING (client_id)
)

SELECT * FROM segmented_enrollments