adamsilverstein/wordpress_INP_offenders.sql

## wordpress_INP_offenders.sql
SELECT
  *
FROM(
  SELECT
    JSON_VALUE(third_party, '$.entity') as entity,
    CAST(APPROX_QUANTILES(CAST(JSON_QUERY(third_party, '$.blockingTime') AS FLOAT64), 100)[offset(75)] as INT64) as p75_blockingTime,
    COUNT(page) as usage
  FROM
    `httparchive.all.pages`,
    UNNEST(technologies) as technologies,
    UNNEST(technologies.categories) as category,
    UNNEST(JSON_QUERY_ARRAY(lighthouse, '$.audits.third-party-summary.details.items')) as third_party
  WHERE
    date = "2023-10-01" AND
    technologies.technology = "WordPress" AND
    category = "CMS" AND
    is_root_page = TRUE
  GROUP BY 1
  ORDER BY usage DESC
  )
  WHERE p75_blockingTime > 100
LIMIT 1000
	SELECT
	*
	FROM(
	SELECT
	JSON_VALUE(third_party, '$.entity') as entity,
	CAST(APPROX_QUANTILES(CAST(JSON_QUERY(third_party, '$.blockingTime') AS FLOAT64), 100)[offset(75)] as INT64) as p75_blockingTime,
	COUNT(page) as usage
	FROM
	`httparchive.all.pages`,
	UNNEST(technologies) as technologies,
	UNNEST(technologies.categories) as category,
	UNNEST(JSON_QUERY_ARRAY(lighthouse, '$.audits.third-party-summary.details.items')) as third_party
	WHERE
	date = "2023-10-01" AND
	technologies.technology = "WordPress" AND
	category = "CMS" AND
	is_root_page = TRUE
	GROUP BY 1
	ORDER BY usage DESC
	)
	WHERE p75_blockingTime > 100
	LIMIT 1000