oembeds_in_wordpress.sql

%%bigquery oembeds
WITH
  WPEmbeds AS (
    SELECT
      url,
      JSON_EXTRACT(payload, '$._cms.wordpress.has_embed_block') AS has_embed_block,
      CAST( JSON_EXTRACT(payload, '$._cms.wordpress.embed_block_count.total') AS FLOAT64 ) AS embed_block_count_total,
      JSON_EXTRACT_ARRAY(payload, '$._cms.wordpress.embed_block_count.total_by_type') AS embed_block_count_total_by_type,

    FROM `httparchive.pages.2023_10_01_*`
  ),
  WordPressOrigins AS (
    SELECT COUNT(DISTINCT (url)) AS wordpress_origins
          FROM `httparchive.technologies.2023_10_01_*`
          WHERE app = 'WordPress'
  ),
  CountedEmbeds AS (
    SELECT
      COUNT( DISTINCT url ) AS origins,
      COUNT( DISTINCT IF( has_embed_block="true", url, NULL ) ) AS has_embed,
      AVG( embed_block_count_total ) as average_embed_block_count,
      STDDEV( embed_block_count_total ) as stddev_embed_block_count,

    FROM
      WPEmbeds

  )
SELECT
  origins,
  wordpress_origins,
  has_embed,
  has_embed/wordpress_origins * 100 AS embed_wp_percentage,
  average_embed_block_count,
  stddev_embed_block_count
FROM CountedEmbeds
CROSS JOIN WordPressOrigins

Results

index	origins	wordpress_origins	has_embed	embed_wp_percentage	average_embed_block_count	stddev_embed_block_count
0	17357030	5845162	58376	1.00%	0.008839441191911103	0.4008374329243984