cms ratio % of total sites over time

cms ratio percent of total sites over time.sql

WITH
  sites AS (
    SELECT
      page AS url,
      date,
      category
    FROM
      `httparchive.all.pages`,
      UNNEST(technologies) AS technologies,
      UNNEST(technologies.categories) AS category
    WHERE
      date >= '2022-01-01'
  )
SELECT
  date,
  COUNT(DISTINCT url) AS all_sites,
  COUNT(DISTINCT (IF(category = 'CMS', url, NULL))) AS cms_sites,
  COUNT(DISTINCT (IF(category = 'CMS', url, NULL))) / COUNT(DISTINCT url) AS cms_ratio
FROM sites
GROUP BY date
ORDER BY date ASC

Results:

date	all_sites	cms_sites	cms_ratio
2023-01-01	37464777	18681321	49.86%
2023-02-01	35460747	17566470	49.54%
2023-03-01	38574419	19498578	50.55%
2023-04-01	38499178	19432227	50.47%
2023-05-01	38366493	19286142	50.27%
2023-06-01	38321756	19280374	50.31%
2023-07-01	37578614	18927759	50.37%
2023-08-01	37292646	18692660	50.12%
2023-09-01	37561748	18780303	50.00%
2023-10-01	38379904	19384036	50.51%
2023-11-01	38443290	19308038	50.22%
2023-12-01	37125625	18899329	50.91%
2024-01-01	34910927	17547042	50.26%
2024-02-01	37644144	19316986	51.31%
2024-03-01	38063292	19572894	51.42%
2024-04-01	38118769	19545825	51.28%
2024-05-01	37975709	19565207	51.52%
2024-06-01	37752289	19362686	51.29%
2024-07-01	37313231	21199386	56.81%
2024-08-01	35542879	20162367	56.73%