Query list of URLs in HTTP Archive which use Image Prioritizer

image-prioritizer-urls.sql

CREATE TEMPORARY FUNCTION IS_CMS(technologies ARRAY<STRUCT<technology STRING, categories ARRAY<STRING>, info ARRAY<STRING>>>, cms STRING, version STRING) RETURNS BOOL AS (
  EXISTS(
    SELECT * FROM UNNEST(technologies) AS technology, UNNEST(technology.info) AS info
    WHERE technology.technology = cms
    AND (
      version = ""
      OR ENDS_WITH(version, ".x") AND (STARTS_WITH(info, RTRIM(version, "x")) OR info = RTRIM(version, ".x"))
      OR info = version
    )
  )
);
SELECT DISTINCT
  page,
  is_root_page,
  rank
FROM
`httparchive.all.pages`
  WHERE
    date = '2024-11-01'
    AND IS_CMS(technologies, 'WordPress', '')
    AND IS_CMS(technologies, 'Image Prioritizer', '')
ORDER BY rank ASC