Counts the sites that delivers hosted fonts

popular_hosts_fonts.sql

#standardSQL

-- counts the host_url of a given font

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 value AS host, count
FROM (
  SELECT APPROX_TOP_COUNT(NET.HOST(url), 500) AS font_host
  FROM processedTable
  WHERE hosted = true
), UNNEST(font_host)

You can modify this query to handle other metrics, like find the top 25 font formats by querying the ext field:

SELECT value AS ext, count
FROM (
  SELECT APPROX_TOP_COUNT(LOWER(ext), 25) AS font_extension
  FROM `httparchive.sample_data.summary_requests_desktop_1k`
  WHERE type = 'font'
), UNNEST(font_extension)

Wow, i made a lot more hehe... But thanks for this :)