1 - Queries.md

Queries

These are the queries we used for the CMS market share report.

2 - cms-marketshare.sql

WITH site_cms AS (
	SELECT
		url,
		COALESCE(
			MAX(IF(category = 'CMS', app, NULL)),
			MAX(IF(category = 'Ecommerce' AND app != 'Cart Functionality', app, NULL)),
			'Unrecognized'
		) AS cms_name
	FROM
		`httparchive.technologies.2024_05_01_mobile`
	GROUP BY
		url
)
SELECT
	cms_name,
	COUNT(*) AS count,
	ROUND( 100 * ( COUNT(*) / SUM(COUNT(*)) OVER() ), 2 ) AS market_share
FROM
	site_cms
GROUP BY
	cms_name
ORDER BY
	count DESC
LIMIT 100

4 - page-builders.sql

WITH site_builder AS (
	SELECT
		url,
		COALESCE(
			MAX(IF(category = 'Page builders', app, NULL)),
			'Unrecognized'
		) AS builder_name
	FROM
		`httparchive.technologies.2024_05_01_mobile`
	GROUP BY
		url
)
SELECT
	builder_name,
	COUNT(*) AS count,
	ROUND( 100 * ( COUNT(*) / SUM(COUNT(*)) OVER() ), 2 ) AS market_share
FROM
	site_builder
GROUP BY
	builder_name
ORDER BY
	count DESC
LIMIT 100

5 - SEO-plugins.sql

WITH site_plugin AS (
	SELECT
		url,
		COALESCE(
			MAX(IF(category = 'WordPress plugins' AND app IN ('All in One SEO Pack', 'RankMath SEO', 'Slim SEO', 'The SEO Framework', 'Yoast SEO'), app, NULL)),
			'Unrecognized'
		) AS plugin_name
	FROM
		`httparchive.technologies.2024_05_01_mobile`
	GROUP BY
		url
)
SELECT
	plugin_name,
	COUNT(*) AS count,
	ROUND( 100 * ( COUNT(*) / SUM(COUNT(*)) OVER() ), 2 ) AS market_share
FROM
	site_plugin
GROUP BY
	plugin_name
ORDER BY
	count DESC
LIMIT 100