get-most-popular-frame-hosts.sql

CREATE TEMPORARY FUNCTION
  getIframeHosts(payload STRING)
  RETURNS ARRAY<STRING>
  LANGUAGE js AS '''
try {
  const almanac = JSON.parse(payload);
  return almanac.iframes.iframes.nodes.map(x => x.src).filter(x => x).map(x => x.replace(new RegExp('^//'), 'https://')).filter(x => x.indexOf('http') === 0);
} catch (e) {
  return [];
}
''';

WITH
  hosts AS (
  SELECT
    NET.HOST(src) AS host,
    COUNT(1) AS num,
    ARRAY_AGG(STRUCT(url,
        src)
    ORDER BY
      RAND()
    LIMIT
      50) AS sample,
  FROM (
    SELECT
      url,
      getIframeHosts(JSON_EXTRACT_SCALAR(payload, '$._almanac')) AS sources
    FROM
      `httparchive.pages.2022_09_01_desktop` ),
    UNNEST(sources) AS src
  GROUP BY
    host ),

  totals AS (
  SELECT
    COUNT(1) AS total
  FROM
    `httparchive.pages.2022_09_01_desktop` )

SELECT
  host,
  num,
  num / total * 100.0 AS pct,
  sample
FROM
  hosts
FULL OUTER JOIN
  totals
ON
  (TRUE)
ORDER BY
  num DESC
LIMIT
  10000