checks if a requested font url (using content-type) is a hosted font (outside) or local (meaning part of the url)

check-font-if-local-or-hosted.sql

#standardSQL

-- counts the local and hosted fonts

WITH 
  summary_pages AS (
    SELECT * FROM `httparchive.sample_data.summary_pages_desktop_1k` 
    UNION ALL
    SELECT * FROM `httparchive.sample_data.summary_pages_mobile_1k`
  ),
  summary_requests AS (
    SELECT * FROM `httparchive.sample_data.summary_requests_desktop_1k` 
    UNION ALL
    SELECT * FROM `httparchive.sample_data.summary_requests_mobile_1k`
  ),
  merge_table AS (
    SELECT * FROM summary_requests JOIN (
      SELECT pageid, url AS page_url FROM summary_pages
    ) USING (pageid)
  ),
  processedTable AS (
    SELECT (NET.HOST(url) != NET.HOST(page_url)) as hosted, url, page_url
    FROM merge_table WHERE type = 'font'  
  )
SELECT 
  COUNTIF(hosted = true) AS hosted, COUNTIF(hosted = false) AS local
FROM processedTable