August 19, 2021 Web Almanac live stream queries

cms-cwv.sql

SELECT
  client,
  app,
  origins,
  SAFE_DIVIDE(origins_with_good_cwv, origins_eligible_for_cwv) AS pct_good_cwv
FROM
  `httparchive.core_web_vitals.technologies`
WHERE
  date = "2021-07-01" AND
  REGEXP_CONTAINS(categories, r'CMS')
ORDER BY
  origins DESC

css-focus-visible.sql

#standardSQL
CREATE TEMPORARY FUNCTION getSelectorParts(css STRING)
RETURNS STRUCT<
  class ARRAY<STRING>,
  id ARRAY<STRING>,
  attribute ARRAY<STRING>,
  pseudo_class ARRAY<STRING>,
  pseudo_element ARRAY<STRING>
>
LANGUAGE js
OPTIONS (library = "gs://httparchive/lib/css-utils.js")
AS '''
try {
  function compute(ast) {
    let ret = {
      class: {},
      id: {},
      attribute: {},
      "pseudo-class": {},
      "pseudo-element": {}
    };

    walkSelectors(ast, selector => {
      let sast = parsel.parse(selector, {list: false});

      parsel.walk(sast, node => {
        if (node.type in ret) {
          incrementByKey(ret[node.type], node.name);
        }
      }, {subtree: true});
    });

    for (let type in ret) {
      ret[type] = sortObject(ret[type]);
    }

    return ret;
  }

  function unzip(obj) {
    return Object.entries(obj).filter(([name, value]) => {
      return !isNaN(value);
    }).map(([name, value]) => name);
  }

  const ast = JSON.parse(css);
  let parts = compute(ast);
  return {
    class: unzip(parts.class),
    id: unzip(parts.id),
    attribute: unzip(parts.attribute),
    pseudo_class: unzip(parts['pseudo-class']),
    pseudo_element: unzip(parts['pseudo-element'])
  }
} catch (e) {
  return null;
}
''';

SELECT
  client,
  COUNTIF(num_focus_visible > 0) AS has_focus_visible,
  COUNT(0) AS total,
  COUNTIF(num_focus_visible > 0) / COUNT(0) AS pct_pages_focus_visible
FROM (
  SELECT
    client,
    page,
    COUNTIF(pseudo_class = 'focus-visible') AS num_focus_visible
  FROM
    `httparchive.sample_data.parsed_css`,
    UNNEST(getSelectorParts(css).pseudo_class) AS pseudo_class
  GROUP BY
    client,
    page)
GROUP BY
  client

lazy-loading.sql

CREATE TEMP FUNCTION getLoadingAttr(loading STRING) RETURNS ARRAY<STRING> LANGUAGE js AS '''
try {
  loading = JSON.parse(loading);
  return Array.from(new Set(loading));
} catch (e) {
  return [];
}
''';

SELECT
  _TABLE_SUFFIX AS client,
  COUNT(DISTINCT IF(attr = 'lazy', url, NULL)) AS pages_with_lazy_loading,
  COUNT(DISTINCT url) AS total,
  COUNT(DISTINCT IF(attr = 'lazy', url, NULL)) / COUNT(DISTINCT url) AS pct
FROM
  `httparchive.pages.2021_07_01_*`
LEFT JOIN
  UNNEST(getLoadingAttr(JSON_EXTRACT_SCALAR(payload, "$['_img-loading-attr']"))) AS attr
GROUP BY
  client

parse-css.sql

CREATE TEMP FUNCTION parseCSS(stylesheet STRING)
RETURNS STRING
LANGUAGE js
OPTIONS (library = "gs://httparchive/lib/parse-css.js")
AS '''
  try {
   var css = parse(stylesheet)
   return JSON.stringify(css);
  } catch (e) {
    '';
  }
''';

SELECT parseCSS('''
div:focus-visible {
  outline: none;
}
''')

service-worker-rank.sql

SELECT
  client,
  rank,
  COUNT(DISTINCT IF(feature = 'ServiceWorkerControlledPage', url, NULL)) AS sw_pages,
  COUNT(DISTINCT url) AS total,
  COUNT(DISTINCT IF(feature = 'ServiceWorkerControlledPage', url, NULL)) / COUNT(DISTINCT url) AS pct
FROM
  `httparchive.blink_features.features`
WHERE
  yyyymmdd = '2021-07-01'
GROUP BY
  client,
  rank