autogen.yaml

queries:
  - id: 318
    name: "DESCRIBE crash_aggregates"
    data_source: "Presto"
    schedule: null
    api_key: "d92720bb35301e28362dcc4d94ad789dbcb32773"
    query: |
      DESCRIBE crash_aggregates
  - id: 200
    name: "FxDesktop Nightly: Crashes by Date"
    data_source: "Presto"
    schedule: "08:00"
    api_key: "b6e185e0535b32b5c6da5272ae59d8f5e08da1c4"
    query: |
      WITH channel_data AS (
        SELECT *
        FROM crash_aggregates
        WHERE
          CAST(submission_date AS date) >= date_add('day', -90, current_date)
          AND CAST(activity_date AS date) >= date_add('day', -90, current_date)
          AND dimensions['application'] = 'Firefox'
          AND dimensions['channel'] = 'nightly'
          AND dimensions['experiment_id'] IS NULL
      ),
      crash_counts AS (
        SELECT
          activity_date,
          SUM(stats['usage_hours']) / 1000 AS usage_khours,
          SUM(stats['main_crashes']) AS main_crashes,
          SUM(stats['content_crashes']) AS content_crashes,
          SUM(stats['content_shutdown_crashes']) AS content_shutdown_crashes,
          SUM(stats['plugin_crashes']) AS npapi_plugin_crashes,
          SUM(stats['gmplugin_crashes']) AS media_plugin_crashes
        FROM channel_data
        GROUP BY activity_date
      ),
      versions_by_date AS (
        SELECT
          activity_date,
          dimensions['build_version'] AS version,
          ROW_NUMBER() OVER (
            PARTITION BY activity_date
            ORDER BY SUM(stats['usage_hours']) DESC) AS rank
        FROM channel_data
        GROUP BY activity_date, dimensions['build_version']
      ),
      leading_version_by_date AS (
        SELECT
          activity_date,
          version AS leading_version
        FROM versions_by_date
        WHERE rank = 1
      ),
      counts_over_time AS (
        SELECT
          activity_date,
          usage_khours,
          main_crashes,
          content_crashes,
          content_shutdown_crashes,
          npapi_plugin_crashes,
          media_plugin_crashes,
          SUM(main_crashes) OVER
            (ORDER BY activity_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
            AS main_crashes_weekly,
          SUM(content_crashes) OVER
            (ORDER BY activity_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
            AS content_crashes_weekly,
          SUM(content_shutdown_crashes) OVER
            (ORDER BY activity_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
            AS content_shutdown_crashes_weekly,
          SUM(npapi_plugin_crashes) OVER
            (ORDER BY activity_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
            AS npapi_plugin_crashes_weekly,
          SUM(media_plugin_crashes) OVER
            (ORDER BY activity_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
            AS media_plugin_crashes_weekly
        FROM crash_counts
      )
      SELECT
        cot.*,
        leading_version
      FROM
        counts_over_time AS cot
        LEFT OUTER JOIN leading_version_by_date AS lvbd
          ON cot.activity_date = lvbd.activity_date
      ORDER BY cot.activity_date ASC
  - id: 199
    name: "FxDesktop DevEdition: Crashes By Date"
    data_source: "Presto"
    schedule: "08:00"
    api_key: "705e86e3e080611aa21e693ba0187190af902c8b"
    query: |
      WITH channel_data AS (
        SELECT *
        FROM crash_aggregates
        WHERE
          CAST(submission_date AS date) >= date_add('day', -90, current_date)
          AND CAST(activity_date AS date) >= date_add('day', -90, current_date)
          AND dimensions['application'] = 'Firefox'
          AND dimensions['channel'] = 'aurora'
          AND dimensions['experiment_id'] IS NULL
      ),
      crash_counts AS (
        SELECT
          activity_date,
          SUM(stats['usage_hours']) / 1000 AS usage_khours,
          SUM(stats['main_crashes']) AS main_crashes,
          SUM(stats['content_crashes']) AS content_crashes,
          SUM(stats['content_shutdown_crashes']) AS content_shutdown_crashes,
          SUM(stats['plugin_crashes']) AS npapi_plugin_crashes,
          SUM(stats['gmplugin_crashes']) AS media_plugin_crashes
        FROM channel_data
        GROUP BY activity_date
      ),
      versions_by_date AS (
        SELECT
          activity_date,
          dimensions['build_version'] AS version,
          ROW_NUMBER() OVER (
            PARTITION BY activity_date
            ORDER BY SUM(stats['usage_hours']) DESC) AS rank
        FROM channel_data
        GROUP BY activity_date, dimensions['build_version']
      ),
      leading_version_by_date AS (
        SELECT
          activity_date,
          version AS leading_version
        FROM versions_by_date
        WHERE rank = 1
      ),
      counts_over_time AS (
        SELECT
          activity_date,
          usage_khours,
          main_crashes,
          content_crashes,
          npapi_plugin_crashes,
          media_plugin_crashes,
          SUM(main_crashes) OVER
            (ORDER BY activity_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
            AS main_crashes_weekly,
          SUM(content_crashes) OVER
            (ORDER BY activity_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
            AS content_crashes_weekly,
          SUM(content_shutdown_crashes) OVER
            (ORDER BY activity_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
            AS content_shutdown_crashes_weekly,
          SUM(npapi_plugin_crashes) OVER
            (ORDER BY activity_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
            AS npapi_plugin_crashes_weekly,
          SUM(media_plugin_crashes) OVER
            (ORDER BY activity_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
            AS media_plugin_crashes_weekly
        FROM crash_counts
      )
      SELECT
        cot.*,
        leading_version
      FROM
        counts_over_time AS cot
        LEFT OUTER JOIN leading_version_by_date AS lvbd
          ON cot.activity_date = lvbd.activity_date
      ORDER BY cot.activity_date ASC
  - id: 322
    name: "FxDesktop Beta: Crashes By Date"
    data_source: "Presto"
    schedule: "08:00"
    api_key: "82551524acb38eb45f20d203b0004831b54c9696"
    query: |
      WITH channel_data AS (
        SELECT *
        FROM crash_aggregates
        WHERE
          CAST(submission_date AS date) >= date_add('day', -90, current_date)
          AND CAST(activity_date AS date) >= date_add('day', -90, current_date)
          AND dimensions['application'] = 'Firefox'
          AND dimensions['channel'] = 'beta'
          AND dimensions['experiment_id'] IS NULL
          AND (dimensions['e10s_enabled'] != 'True' OR dimensions['e10s_enabled'] IS NULL)
      ),
      crash_counts AS (
        SELECT
          activity_date,
          SUM(stats['usage_hours']) / 1000 AS usage_khours,
          SUM(stats['main_crashes']) AS main_crashes,
          SUM(stats['content_crashes']) AS content_crashes,
          SUM(stats['content_shutdown_crashes']) AS content_shutdown_crashes,
          SUM(stats['plugin_crashes']) AS npapi_plugin_crashes,
          SUM(stats['gmplugin_crashes']) AS media_plugin_crashes
        FROM channel_data
        GROUP BY activity_date
      ),
      versions_by_date AS (
        SELECT
          activity_date,
          dimensions['build_version'] AS version,
          ROW_NUMBER() OVER (
            PARTITION BY activity_date
            ORDER BY SUM(stats['usage_hours']) DESC) AS rank
        FROM channel_data
        GROUP BY activity_date, dimensions['build_version']
      ),
      leading_version_by_date AS (
        SELECT
          activity_date,
          version AS leading_version
        FROM versions_by_date
        WHERE rank = 1
      ),
      counts_over_time AS (
        SELECT
          activity_date,
          usage_khours,
          main_crashes,
          content_crashes,
          content_shutdown_crashes,
          npapi_plugin_crashes,
          media_plugin_crashes,
          SUM(main_crashes) OVER
            (ORDER BY activity_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
            AS main_crashes_weekly,
          SUM(content_crashes) OVER
            (ORDER BY activity_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
            AS content_crashes_weekly,
          SUM(content_shutdown_crashes) OVER
            (ORDER BY activity_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
            AS content_shutdown_crashes_weekly,
          SUM(npapi_plugin_crashes) OVER
            (ORDER BY activity_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
            AS npapi_plugin_crashes_weekly,
          SUM(media_plugin_crashes) OVER
            (ORDER BY activity_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
            AS media_plugin_crashes_weekly
        FROM crash_counts
      )
      SELECT
        cot.*,
        leading_version
      FROM
        counts_over_time AS cot
        LEFT OUTER JOIN leading_version_by_date AS lvbd
          ON cot.activity_date = lvbd.activity_date
      ORDER BY cot.activity_date ASC
  - id: 326
    name: "FxDesktop: Comparison of e10s/non-e10s crash rates"
    data_source: "Presto"
    api_key: "d2f89374fbb282f44b299e1267f9c99771f59773"
    schedule: "08:00"
    query: |
      WITH channel_data AS (
        SELECT *
        FROM crash_aggregates
        WHERE
          CAST(submission_date AS date) >= date_add('day', -90, current_date)
          AND CAST(activity_date AS date) >= date_add('day', -90, current_date)
          AND dimensions['application'] = 'Firefox'
          AND dimensions['channel'] = 'beta'
          AND dimensions['experiment_id'] IS NULL
          AND dimensions['e10s_cohort'] IN ('test', 'control')
          AND date_diff('day', date_parse(SUBSTR(dimensions['build_id'], 1, 8), '%Y%m%d'), date_parse(SUBSTR(activity_date, 1, 10), '%Y-%m-%d')) < 21
      ),
      crash_counts AS (
        SELECT
          activity_date,
          dimensions['e10s_cohort'] AS e10s_cohort,
          SUM(stats['usage_hours']) / 1000 AS usage_khours,
          SUM(stats['main_crashes']) AS main_crashes,
          SUM(stats['content_crashes']) AS content_crashes,
          SUM(stats['content_shutdown_crashes']) AS content_shutdown_crashes,
          SUM(stats['plugin_crashes']) AS npapi_plugin_crashes,
          SUM(stats['gmplugin_crashes']) AS media_plugin_crashes
        FROM channel_data
        GROUP BY activity_date, dimensions['e10s_cohort']
      ),
      nonxp_crash_counts AS (
        SELECT
          activity_date,
          dimensions['e10s_cohort'] || '_without_xp' AS e10s_cohort,
          SUM(stats['usage_hours']) / 1000 AS usage_khours,
          SUM(stats['main_crashes']) AS main_crashes,
          SUM(stats['content_crashes']) AS content_crashes,
          SUM(stats['content_shutdown_crashes']) AS content_shutdown_crashes,
          SUM(stats['plugin_crashes']) AS npapi_plugin_crashes,
          SUM(stats['gmplugin_crashes']) AS media_plugin_crashes
        FROM channel_data
        WHERE dimensions['os_name'] != 'Windows_NT' OR
          TRY_CAST(SPLIT_PART(dimensions['os_version'], '.', 1) AS BIGINT) >= 6
        GROUP BY activity_date, dimensions['e10s_cohort']
      ),
      versions_by_date AS (
        SELECT
          activity_date,
          dimensions['build_version'] AS version,
          ROW_NUMBER() OVER (
            PARTITION BY activity_date
            ORDER BY SUM(stats['usage_hours']) DESC) AS rank
        FROM channel_data
        GROUP BY activity_date, dimensions['build_version']
      ),
      leading_version_by_date AS (
        SELECT
          activity_date,
          version AS leading_version
        FROM versions_by_date
        WHERE rank = 1
      ),
      counts_over_time AS (
        SELECT
          activity_date,
          e10s_cohort,
          usage_khours,
          main_crashes,
          content_crashes,
          content_shutdown_crashes,
          npapi_plugin_crashes,
          media_plugin_crashes
        FROM (SELECT * FROM crash_counts UNION ALL
          SELECT * FROM nonxp_crash_counts)
        WHERE usage_khours > 1000
      )
      SELECT
        cot.*,
        leading_version
      FROM
        counts_over_time AS cot
        LEFT OUTER JOIN leading_version_by_date AS lvbd
          ON cot.activity_date = lvbd.activity_date
      ORDER BY cot.activity_date ASC