font-face-api.sql

#standardSQL

-- counts the number of usage of FontFace API

WITH 
  response_bodies AS (
    SELECT * FROM `httparchive.sample_data.response_bodies_desktop_1k` 
--     UNION ALL
--     SELECT * FROM `httparchive.sample_data.response_bodies_mobile_1k`
  ),
  temp AS (
    SELECT 
      ARRAY_LENGTH(REGEXP_EXTRACT_ALL(body, r"new FontFace\(")) AS fontface,
      ARRAY_LENGTH(REGEXP_EXTRACT_ALL(body, r"new FontFaceSetLoadEvent\(")) AS fontfacesetloadevent,
      url, 
      page,
      body
    FROM response_bodies 
    WHERE 
      STRPOS(body, "FontFace") > 0 OR 
      STRPOS(body, "FontFaceSet") > 0 OR
      STRPOS(body, "FontFaceSetLoadEvent") > 0
  ),
-- SELECT * from temp
  processedTable AS (
    SELECT 
--       *
      DISTINCT(page),
      SUM(fontface) AS fontface,
      SUM(fontfacesetloadevent) AS fontfacesetloadevent
    FROM temp
    GROUP BY page
  )
SELECT
  SUM(fontface) AS fontface,
  SUM(fontfacesetloadevent) as fontfacesetloadevent
FROM processedTable