webp-size-vs-jpeg.sql

SELECT
  has_webp,
  APPROX_QUANTILES(bytesImg, 1000)[OFFSET(500)] / 1024 / 1024 AS median_img_mbytes
FROM (
  SELECT DISTINCT
    url,
    info AS version
  FROM
    `httparchive.technologies.2021_11_01_mobile`
  WHERE
    app = 'WordPress')
JOIN (
  SELECT
    url,
    has_webp,
    bytesImg
  FROM (  
    SELECT
      pageid,
      COUNTIF(ext = 'webp') > 0 AS has_webp
    FROM
      `httparchive.summary_requests.2021_11_01_mobile`
    GROUP BY
      pageid)
    JOIN (
      SELECT
        pageid,
        url,
        bytesImg
      FROM
        `httparchive.summary_pages.2021_11_01_mobile`)
    USING
      (pageid))
USING
  (url)
GROUP BY
  has_webp