Query behind https://twitter.com/rick_viscomi/status/1532418502295965698

cms-adoption-share.sql

WITH app_list AS (
  SELECT DISTINCT
    app
  FROM
    `httparchive.technologies.2022_05_01_mobile`
  WHERE
    category IN ('CMS')
), category AS (
  SELECT
    _TABLE_SUFFIX,
    *
  FROM
    `httparchive.technologies.*`
  JOIN
    app_list
  USING
    (app)
  WHERE
    ENDS_WITH(_TABLE_SUFFIX, 'mobile')
), total AS (
  SELECT
    _TABLE_SUFFIX,
    COUNT(DISTINCT url) AS websites
  FROM
    category
  GROUP BY
    _TABLE_SUFFIX
), apps AS (
  SELECT
    _TABLE_SUFFIX,
    app,
    COUNT(DISTINCT url) AS websites
  FROM
    category
  GROUP BY
    _TABLE_SUFFIX,
    app
)

SELECT
  REGEXP_REPLACE(_TABLE_SUFFIX, r'(\d{4})_(\d{2})_(\d{2})_.*', r'\1-\2-\3') AS date,
  app,
  apps.websites AS websites,
  total.websites AS total,
  apps.websites / total.websites AS pct_share
FROM
  apps
JOIN
  total
USING
  (_TABLE_SUFFIX)
ORDER BY
  date DESC,
  websites DESC