Calculate the median image size for WordPress pages using/not using WebP images. Change mobile to desktop for desktop sizes.

median_image_bytes_webp.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.2022_07_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.2022_07_01_mobile`
    GROUP BY
      pageid)
    JOIN (
      SELECT
        pageid,
        url,
        bytesImg
      FROM
        `httparchive.summary_pages.2022_07_01_mobile`)
    USING
      (pageid))
USING
  (url)
GROUP BY
  has_webp