/* Which sites use multiple versions of JQuery, and how many? (facepalm) */

SELECT pages.pageid, url, cnt, versions, pages.rank rank FROM [httparchive:runs.2013_06_01_pages] as pages JOIN ( 
  SELECT pageid, count(distinct(version)) cnt, GROUP_CONCAT(version) versions FROM (
    SELECT url, REGEXP_EXTRACT(url, r'googleapis.*jquery\/(\d+\.\d+\.\d+)\/.*\.js') version, pageid 
    FROM [httparchive:runs.2013_06_01_requests] 
    WHERE REGEXP_MATCH(url, r'jquery.*\.js')
    GROUP BY url, pageid, version
  )
  WHERE version IS NOT NULL
  GROUP by pageid
  HAVING cnt >= 2
) as lib ON lib.pageid = pages.pageid
WHERE rank is NOT NULL
ORDER BY rank asc