gistfile1.txt

Validate bug-1671620-message-homepage-remediation-search-value-props-exper-release-82-83.toml
Error evaluating SQL:
   1 
   2     WITH analysis_windows AS (
   3         (SELECT 0 AS analysis_window_start, 20 AS analysis_window_end)
   4     ),
   5     raw_enrollments AS (    SELECT
   6         client_id,
   7         mozfun.map.get_key(experiments, "bug-1671620-message-homepage-remediation-search-value-props-exper-release-82-83").branch AS branch,
   8         MIN(DATE(submission_timestamp)) AS enrollment_date,
   9         COUNT(*) AS num_enrollments
  10     FROM `moz-fx-data-shared-prod`.messaging_system.cfr
  11     WHERE
  12         message_id LIKE "bug-1671620-message-homepage-remediation-search-value-props-exper-release-82-83:%"
  13         AND DATE(submission_timestamp) BETWEEN "2020-10-27" AND "2020-11-03"
  14     GROUP BY client_id, branch),
  15     segmented_enrollments AS (
  16         SELECT
  17             raw_enrollments.*,
  18 
  19         FROM raw_enrollments
  20 
  21         ),
  22     enrollments AS (
  23         SELECT
  24             e.*,
  25             aw.*
  26         FROM segmented_enrollments e
  27         CROSS JOIN analysis_windows aw
  28     )
  29     SELECT
  30         enrollments.*,
  31         ds_0.search_count,
  32         ds_0.organic_search_count,
  33         ds_0.tagged_follow_on_search_count,
  34         ds_0.separate_search_engine,
  35         ds_0.ad_clicks,
  36         ds_0.searches_with_ads,
  37         ds_0.tagged_search_count,
  38         ds_1.unenroll,
  39         ds_1.normandy_events_has_contradictory_branch,
  40         ds_1.normandy_events_has_non_enrolled_data,
  41         ds_2.ever_default_homepage,
  42         ds_2.main_has_contradictory_branch,
  43         ds_2.main_has_non_enrolled_data,
  44         ds_3.retained,
  45         ds_3.uri_count,
  46         ds_3.active_hours,
  47         ds_3.clients_daily_has_contradictory_branch,
  48         ds_3.clients_daily_has_non_enrolled_data
  49     FROM enrollments
  50         LEFT JOIN (
  51         SELECT
  52             e.client_id,
  53             e.analysis_window_start,
  54             e.analysis_window_end,
  55             COALESCE(SUM(sap), 0) AS search_count,
  56             COALESCE(SUM(organic), 0) AS organic_search_count,
  57             COALESCE(SUM(tagged_follow_on), 0) AS tagged_follow_on_search_count,
  58             COALESCE(ANY_VALUE(default_search_engine != default_private_search_engine), false) AS separate_search_engine,
  59             COALESCE(SUM(ad_click), 0) AS ad_clicks,
  60             COALESCE(SUM(search_with_ads), 0) AS searches_with_ads,
  61             COALESCE(SUM(tagged_sap), 0) AS tagged_search_count
  62         FROM enrollments e
  63             LEFT JOIN `moz-fx-data-shared-prod.search.search_clients_daily` ds
  64                 ON ds.client_id = e.client_id
  65                 AND ds.submission_date BETWEEN '2020-10-27' AND '2020-11-23'
  66                 AND ds.submission_date BETWEEN
  67                     DATE_ADD(e.enrollment_date, interval e.analysis_window_start day)
  68                     AND DATE_ADD(e.enrollment_date, interval e.analysis_window_end day)
  69 
  70         GROUP BY e.client_id, e.analysis_window_start, e.analysis_window_end
  71         ) ds_0 USING (client_id, analysis_window_start, analysis_window_end)
  72 
  73     LEFT JOIN (
  74         SELECT
  75             e.client_id,
  76             e.analysis_window_start,
  77             e.analysis_window_end,
  78             COALESCE(LOGICAL_OR(
  79                 event_category = 'normandy'
  80                 AND event_method = 'unenroll'
  81                 AND event_string_value = 'bug-1671620-message-homepage-remediation-search-value-props-exper-release-82-83'
  82             ), FALSE) AS unenroll,
  83             COALESCE(LOGICAL_OR(`mozfun.map.get_key`(
  84                 ds.experiments, 'bug-1671620-message-homepage-remediation-search-value-props-exper-release-82-83'
  85             ).branch != e.branch), FALSE) AS normandy_events_has_contradictory_branch,
  86             COALESCE(LOGICAL_OR(`mozfun.map.get_key`(
  87                 ds.experiments, 'bug-1671620-message-homepage-remediation-search-value-props-exper-release-82-83'
  88             ).branch IS NULL), FALSE) AS normandy_events_has_non_enrolled_data
  89         FROM enrollments e
  90             LEFT JOIN (
  91         SELECT
  92             *
  93         FROM `moz-fx-data-shared-prod`.telemetry.events
  94         WHERE event_category = 'normandy'
  95     ) ds
  96                 ON ds.client_id = e.client_id
  97                 AND ds.submission_date BETWEEN '2020-10-27' AND '2020-11-23'
  98                 AND ds.submission_date BETWEEN
  99                     DATE_ADD(e.enrollment_date, interval e.analysis_window_start day)
 100                     AND DATE_ADD(e.enrollment_date, interval e.analysis_window_end day)
 101                 AND (
 102                     ds.submission_date != e.enrollment_date
 103                     OR `mozfun.map.get_key`(
 104                         ds.experiments, 'bug-1671620-message-homepage-remediation-search-value-props-exper-release-82-83'
 105                     ).branch IS NOT NULL
 106             )
 107         GROUP BY e.client_id, e.analysis_window_start, e.analysis_window_end
 108         ) ds_1 USING (client_id, analysis_window_start, analysis_window_end)
 109 
 110     LEFT JOIN (
 111         SELECT
 112             e.client_id,
 113             e.analysis_window_start,
 114             e.analysis_window_end,
 115             LOGICAL_OR(mozfun.map.get_key(environment.settings.user_prefs, 'browser.startup.homepage') IS NULL) AS ever_default_homepage,
 116             COALESCE(LOGICAL_OR(`mozfun.map.get_key`(
 117                 ds.experiments, 'bug-1671620-message-homepage-remediation-search-value-props-exper-release-82-83'
 118             ).branch != e.branch), FALSE) AS main_has_contradictory_branch,
 119             COALESCE(LOGICAL_OR(`mozfun.map.get_key`(
 120                 ds.experiments, 'bug-1671620-message-homepage-remediation-search-value-props-exper-release-82-83'
 121             ).branch IS NULL), FALSE) AS main_has_non_enrolled_data
 122         FROM enrollments e
 123             LEFT JOIN (
 124                 SELECT
 125                     *,
 126                     DATE(submission_timestamp) AS submission_date,
 127                     environment.experiments
 128                 FROM `moz-fx-data-shared-prod`.telemetry.main
 129             ) ds
 130                 ON ds.client_id = e.client_id
 131                 AND ds.submission_date BETWEEN '2020-10-27' AND '2020-11-23'
 132                 AND ds.submission_date BETWEEN
 133                     DATE_ADD(e.enrollment_date, interval e.analysis_window_start day)
 134                     AND DATE_ADD(e.enrollment_date, interval e.analysis_window_end day)
 135                 AND (
 136                     ds.submission_date != e.enrollment_date
 137                     OR `mozfun.map.get_key`(
 138                         ds.experiments, 'bug-1671620-message-homepage-remediation-search-value-props-exper-release-82-83'
 139                     ).branch IS NOT NULL
 140             )
 141         GROUP BY e.client_id, e.analysis_window_start, e.analysis_window_end
 142         ) ds_2 USING (client_id, analysis_window_start, analysis_window_end)
 143 
 144     LEFT JOIN (
 145         SELECT
 146             e.client_id,
 147             e.analysis_window_start,
 148             e.analysis_window_end,
 149             COALESCE(SUM(pings_aggregated_by_this_row), 0) > 0 AS retained,
 150             COALESCE(SUM(scalar_parent_browser_engagement_total_uri_count_sum), 0) AS uri_count,
 151             COALESCE(SUM(active_hours_sum), 0) AS active_hours,
 152             COALESCE(LOGICAL_OR(`mozfun.map.get_key`(
 153                 ds.experiments, 'bug-1671620-message-homepage-remediation-search-value-props-exper-release-82-83'
 154             ) != e.branch), FALSE) AS clients_daily_has_contradictory_branch,
 155             COALESCE(LOGICAL_OR(`mozfun.map.get_key`(
 156                 ds.experiments, 'bug-1671620-message-homepage-remediation-search-value-props-exper-release-82-83'
 157             ) IS NULL), FALSE) AS clients_daily_has_non_enrolled_data
 158         FROM enrollments e
 159             LEFT JOIN `moz-fx-data-shared-prod.telemetry.clients_daily` ds
 160                 ON ds.client_id = e.client_id
 161                 AND ds.submission_date BETWEEN '2020-10-27' AND '2020-11-23'
 162                 AND ds.submission_date BETWEEN
 163                     DATE_ADD(e.enrollment_date, interval e.analysis_window_start day)
 164                     AND DATE_ADD(e.enrollment_date, interval e.analysis_window_end day)
 165                 AND (
 166                     ds.submission_date != e.enrollment_date
 167                     OR `mozfun.map.get_key`(
 168                         ds.experiments, 'bug-1671620-message-homepage-remediation-search-value-props-exper-release-82-83'
 169                     ) IS NOT NULL
 170                 )
 171         GROUP BY e.client_id, e.analysis_window_start, e.analysis_window_end
 172         ) ds_3 USING (client_id, analysis_window_start, analysis_window_end)
 173 
 174 

Resources exceeded during query execution: Not enough resources for query planning - too many subqueries or query is too complex.