picture-in-wordpress.sql

WITH picture AS (
  SELECT
    url
  FROM
    `httparchive.pages.2022_01_01_mobile`
  WHERE
    JSON_VALUE(JSON_VALUE(payload, '$._element_count'), '$.picture') IS NOT NULL
), wordpress AS (
  SELECT DISTINCT
    url
  FROM
    `httparchive.technologies.2022_01_01_mobile`
  WHERE
    app = 'WordPress'
), total AS (
  SELECT
    COUNT(0) AS total
  FROM
    wordpress
)

SELECT
  COUNT(0) AS wp_picture,
  (SELECT * FROM total) AS total,
  COUNT(0) / (SELECT * FROM total) AS pct_wp_picture
FROM
  picture
JOIN
  wordpress
USING
  (url)