gistfile1.txt

WITH events AS (
    SELECT
        metadata.isp.name = 'Comcast Cable' AS from_comcast,
        `moz-fx-data-shared-prod`.udf.deanonymize_event(e).*
    FROM
        `moz-fx-data-shared-prod`.telemetry.event
        CROSS JOIN UNNEST(payload.events.dynamic) e
    WHERE DATE(submission_timestamp) BETWEEN '2020-08-11' AND '2020-08-19'
)

, resolver_events AS (
    SELECT
        from_comcast,
        mozfun.map.get_key(event_map_values, "dohtest") AS dohtest,
        mozfun.map.get_key(event_map_values, "whoartthou") AS whoartthou,
        mozfun.map.get_key(event_map_values, "canonical") AS canonical,
        mozfun.map.get_key(event_map_values, "uuid") AS uuid,
        mozfun.map.get_key(event_map_values, "uuidRetries") AS uuid_retries,
    FROM events
    WHERE event_category = 'doh.study.resolverusage'
)

SELECT * FROM resolver_events