#standardSQL
CREATE TEMPORARY FUNCTION getJsLibs(payload STRING)
RETURNS ARRAY<STRUCT<name STRING, version STRING>>
LANGUAGE js AS """
  try {
    const $ = JSON.parse(payload);
    const libs = JSON.parse($['_third-parties']);
    return Array.isArray(libs) ? libs : [];
  } catch (e) {
    return [];
  }
""";

SELECT
  date,
  client,
  lib.name AS library,
  COUNT(DISTINCT url) AS frequency
FROM (
  SELECT
    REPLACE(SUBSTR(_TABLE_SUFFIX, 0, 10), '_', '-') AS date,
    IF(ENDS_WITH(_TABLE_SUFFIX, 'desktop'), 'desktop', 'mobile') AS client,
    url,
    getJsLibs(payload) AS libs
  FROM
    `httparchive.pages.*`),
  UNNEST(libs) AS lib
GROUP BY
  date,
  client,
  library
ORDER BY
  date,
  client,
  library