HTTP archive (Using Bigqueries) queries to know "What are TOP CDNs"

httparchive_biqueries.sql

// These queries were used this HTTP archive (Bigqueries)
// http://bigqueri.es/t/whats-the-percent-of-http-request-that-are-outside-of-the-cdns/477
// HTTP requests with and without CDN

SELECT cdn, num, ROUND(ratio*100) as percent FROM (
  SELECT cdn, COUNT(cdn) as num, RATIO_TO_REPORT(num) OVER() ratio   FROM (
     SELECT CASE 
       WHEN _cdn_provider IN ('')
         THEN 'None'
         ELSE 'CDN'
       END as cdn
      FROM httparchive:runs.2014_11_01_requests,  
           httparchive:runs.2014_11_15_requests,
           httparchive:runs.2014_11_01_requests_mobile,
           httparchive:runs.2014_11_15_requests_mobile
   ) GROUP BY cdn
) ORDER BY percent DESC

// TOP 10 
SELECT provider, round(100*ratio) as percent, num 
FROM (SELECT REGEXP_REPLACE(_cdn_provider,r'^$', 'None') as provider, count(*) as num, RATIO_TO_REPORT(num) OVER() ratio
  FROM httparchive:runs.2014_11_01_requests,  
       httparchive:runs.2014_11_15_requests,
       httparchive:runs.2014_11_01_requests_mobile,
       httparchive:runs.2014_11_15_requests_mobile
  WHERE  _cdn_provider != ''
GROUP BY provider   
)
ORDER BY num desc LIMIT 10 


// TOP 20 hostnames appears with Google CDN
SELECT req_host, count(req_host) as num
FROM httparchive:runs.2014_11_01_requests,  
     httparchive:runs.2014_11_15_requests,
     httparchive:runs.2014_11_01_requests_mobile,
     httparchive:runs.2014_11_15_requests_mobile
WHERE _cdn_provider = 'Google'
GROUP BY req_host
ORDER BY num DESC
LIMIT 20

// CDN Ranking discard HTTP requests for Google CDN and without CDN
SELECT provider, round(100*ratio) as percent, num 
FROM (SELECT REGEXP_REPLACE(_cdn_provider,r'^$', 'None') as provider, count(*) as num, RATIO_TO_REPORT(num) OVER() ratio
  FROM httparchive:runs.2014_11_01_requests,  
       httparchive:runs.2014_11_15_requests,
       httparchive:runs.2014_11_01_requests_mobile,
       httparchive:runs.2014_11_15_requests_mobile
  WHERE _cdn_provider != 'Google' AND _cdn_provider != ''
GROUP BY provider   
)
ORDER BY num desc