elementor count by feature.sql

CREATE TEMP FUNCTION getFeatures(payload STRING)
RETURNS STRING
LANGUAGE js
AS '''
  try {
    var $ = JSON.parse(payload);
    var almanac = JSON.parse($._almanac);
    var generators = almanac['meta-nodes'].nodes.find(node => node.name == 'generator' && node.content.startsWith("Elementor"));
    if ( generators.length == 0 ) {
      return "";
    }
    return generators.content;
  } catch (e) {
    return "";
  }
''';

SELECT
      total / e_dom_optimization / 100 AS pct_e_dom_optimization,
      total / e_optimized_assets_loading / 100 AS pct_e_optimized_assets_loading,
      total / e_optimized_css_loading / 100 AS pct_e_optimized_css_loading,
      total / e_font_icon_svg / 100 AS pct_e_font_icon_svg,
      total / a11y_improvements / 100 AS pct_a11y_improvements,
      total / additional_custom_breakpoints / 100 AS pct_additional_custom_breakpoints,
      total / css_print_method_external / 100 AS pct_css_print_method_external,
      total / google_font_enabled / 100 AS pct_google_font_enabled,
      total / font_display_auto / 100 AS pct_font_display_auto,
    FROM
      (
        SELECT
          COUNT(*) AS total,
          COUNTIF(REGEXP_CONTAINS(features, r'e_dom_optimization')) AS e_dom_optimization,
          COUNTIF(REGEXP_CONTAINS(features, r'e_optimized_assets_loading')) AS e_optimized_assets_loading,
          COUNTIF(REGEXP_CONTAINS(features, r'e_optimized_css_loading')) AS e_optimized_css_loading,
          COUNTIF(REGEXP_CONTAINS(features, r'e_font_icon_svg')) AS e_font_icon_svg,
          COUNTIF(REGEXP_CONTAINS(features, r'additional_custom_breakpoints')) AS additional_custom_breakpoints,
          COUNTIF(REGEXP_CONTAINS(features, r'a11y_improvements')) AS a11y_improvements,
          COUNTIF(REGEXP_CONTAINS(features, r'css_print_method-external')) AS css_print_method_external,
          COUNTIF(REGEXP_CONTAINS(features, r'google_font-enabled')) AS google_font_enabled,
          COUNTIF(REGEXP_CONTAINS(features, r'font_display-auto')) AS font_display_auto,
        FROM
          (
            SELECT
              getFeatures(payload) AS features,
            FROM
              `httparchive.pages.2023_03_01_*`
            JOIN
              (
                SELECT DISTINCT url
                FROM
                  `httparchive.technologies.2023_03_01_*`
                WHERE
                  app = 'Elementor'
                  AND info != ''
              )
              USING (url)
          )
      )