query_event.sql

-- bq --format csv query -n 999999999 < query_event.sql > events_hdd.csv

DECLARE slugs ARRAY<STRUCT<slug STRING, name STRING>> DEFAULT [
    ("bug-1685741-pref-unenrollment-normandy-test-with-no-op-pref-in-fi-nightly-86-100", "string"),
    ("bug-1685740-pref-unenrollment-normandy-test-with-no-op-pref-in-fi-nightly-86-100", "boolean"),
    ("bug-1685742-pref-unenrollment-normandy-test-with-no-op-pref-in-fi-nightly-86-100", "integer")
];
DECLARE start_date DATE DEFAULT "2021-01-12";

WITH events AS (
    SELECT
        DATE(submission_timestamp) AS submission_date,
        environment.system.hdd.profile.type AS hdd,
        environment.system.cpu.count AS logical_cpu,
        `moz-fx-data-shared-prod`.udf.deanonymize_event(e).*
    FROM mozdata.telemetry.event
    CROSS JOIN UNNEST(payload.events.parent) AS e
    WHERE
        DATE(submission_timestamp) >= start_date
        AND normalized_os = "Windows"
)

, experiment_events AS (
    SELECT
        submission_date,
        mozfun.map.get_key(event_map_values, "enrollmentId") AS enrollment_id,
        s.name AS experiment,
        event_method,
        event_object,
        mozfun.map.get_key(event_map_values, "branch") AS branch,
        mozfun.map.get_key(event_map_values, "reason") AS reason,
        hdd,
        logical_cpu,
    FROM events
    CROSS JOIN UNNEST(slugs) s
    WHERE
        event_category = 'normandy'
        AND event_string_value = s.slug
)

SELECT * FROM experiment_events