drupal-webp-usage-by-version-platform.sql

SELECT
  mobile.version,
  ROUND(pct_webp_mobile, 3) AS pct_webp_mobile,
  ROUND(pct_webp_desktop, 3) AS pct_webp_desktop
FROM
  (
    SELECT
      version, pct_webp AS pct_webp_mobile
    FROM
      (
        SELECT
          version,
          COUNTIF(has_webp) AS pages_with_webp,
          COUNT(0) AS pages,
          COUNTIF(has_webp) / COUNT(0) AS pct_webp
        FROM
          (
            SELECT DISTINCT
              url,
          REGEXP_EXTRACT(info, r'(\d+)\.?\d?') AS version,

            FROM
              `httparchive.technologies.2022_05_01_mobile`
            WHERE
              app = 'Drupal'
          )
        JOIN
          (
            SELECT
              url,
              has_webp
            FROM
              (
                SELECT
                  pageid,
                  COUNTIF(ext = 'webp') > 0 AS has_webp
                FROM
                  `httparchive.summary_requests.2022_05_01_mobile`
                GROUP BY
                  pageid
              )
            JOIN
              (
                SELECT
                  pageid,
                  url
                FROM
                  `httparchive.summary_pages.2022_05_01_mobile`
              )
              USING (pageid)
          )
          USING (url)
        WHERE version IS NOT NULL
        GROUP BY
          version
        ORDER BY
          version ASC
      )
    WHERE pages > 1500
  ) AS mobile
JOIN
  (
    SELECT
      version, pct_webp AS pct_webp_desktop
    FROM
      (
        SELECT
          version,
          COUNTIF(has_webp) AS pages_with_webp,
          COUNT(0) AS pages,
          COUNTIF(has_webp) / COUNT(0) AS pct_webp
        FROM
          (
            SELECT DISTINCT
              url,
              REGEXP_EXTRACT(info, r'(\d+)\.?\d?') AS version,
            FROM
              `httparchive.technologies.2022_05_01_desktop`
            WHERE
              app = 'Drupal'
          ) 
        JOIN
          (
            SELECT
              url,
              has_webp
            FROM
              (
                SELECT
                  pageid,
                  COUNTIF(ext = 'webp') > 0 AS has_webp
                FROM
                  `httparchive.summary_requests.2022_05_01_desktop`
                GROUP BY
                  pageid
              )
            JOIN
              (
                SELECT
                  pageid,
                  url
                FROM
                  `httparchive.summary_pages.2022_05_01_desktop`
              )
              USING (pageid)
          )
          USING (url)
        WHERE version IS NOT NULL
        GROUP BY
          version
        ORDER BY
          version ASC
      )
    WHERE pages > 1500
  ) AS desktop
  ON mobile.version = desktop.version
  ORDER BY mobile.version DESC