font_type_count.sql

#standardSQL

-- counts the font types

WITH 
  summary_requests AS (
    SELECT * FROM `httparchive.sample_data.summary_requests_desktop_1k` 
    UNION ALL
    SELECT * FROM `httparchive.sample_data.summary_requests_mobile_1k`
  ),
  processedTable AS (
    SELECT 
      (
        IF(STRPOS(mimeType, "font") > 0,
          -- get the type of font from the content-type
          REGEXP_REPLACE(
            REGEXP_REPLACE(mimeType, r"^(font\/x\-)|(font\/font\-)|(font\/)|(application\/font\-)|(application\/x\-font\-)", ""),
            r"^x\-",
            ""
          ),
          -- hack: get the type of font from the file name
          ARRAY_REVERSE(
            SPLIT(
              SPLIT(
                ARRAY_REVERSE(
                  SPLIT(url, "/")
                )[OFFSET(0)],
                "?"
              )[OFFSET(0)],
              "."
            )
          )[OFFSET(0)]           
        )
      ) AS font,
      *
    FROM summary_requests WHERE type = 'font'  
  )
SELECT
  DISTINCT(font), 
  COUNT(font) AS count
FROM processedTable
GROUP BY font
ORDER BY count DESC