wordpress-defer-async-by-version.sql

# Breakdown of scripts using Async, Defer, Module or NoModule attributes.  Also breakdown of inline vs external scripts
CREATE TEMPORARY FUNCTION getScripts(payload STRING)
RETURNS
  STRUCT<
    total INT64,
    inline INT64,
    src INT64,
    async INT64,
    defer INT64,
    async_and_defer INT64,
    type_module INT64,
    nomodule INT64>
LANGUAGE js
AS '''
try {
  var $ = JSON.parse(payload);
  var javascript = JSON.parse($._javascript);
  return javascript.script_tags;
} catch (e) {
  return {};
}
''';

WITH
  wordpress AS (
    SELECT DISTINCT
      url,
      info
    FROM
      `httparchive.technologies.2021_10_01_*`
    WHERE
      app = 'WordPress'
  )
SELECT
  client,
  REGEXP_EXTRACT(info, r'(\d\.\d+)') AS version,
  SUM(script.defer) / NULLIF( SUM(script.total), 0) AS pct_deferred,  
  SUM(script.total) AS total_scripts,
  SUM(script.inline) AS inline_script,
  SUM(script.src) AS external_script,
  SUM(script.src) / NULLIF( SUM(script.total), 0) AS pct_external_script,
  SUM(script.inline) / NULLIF( SUM(script.total), 0) AS pct_inline_script,
  SUM(script.async) AS async,
  SUM(script.defer) AS defer,
  SUM(script.async_and_defer) AS async_and_defer,
  SUM(script.type_module) AS module,
  SUM(script.nomodule) AS nomodule,
  SUM(script.async) / NULLIF( SUM(script.src), 0) AS pct_external_async,
  SUM(script.defer) / NULLIF( SUM(script.src), 0) AS pct_external_defer,
  SUM(script.async_and_defer) / NULLIF( SUM(script.src), 0) AS pct_external_async_defer,
  SUM(script.type_module) / NULLIF( SUM(script.src), 0) AS pct_external_module,
  SUM(script.nomodule) / NULLIF( SUM(script.src), 0) AS pct_external_nomodule
FROM
  (
    SELECT
      _TABLE_SUFFIX AS client,
      getScripts(payload) AS script,
      info
    FROM
      `httparchive.pages.2021_10_01_*`
    JOIN wordpress
      USING (url)
  )
GROUP BY
  version, client