almanac.httparchive.org/sql/2025/third-parties/a11y_overall_tech_usage_by_rank.sql at 217f2ea4c2e366dbba5f867acaa48f0e7c3dab50 · jazlan01/almanac.httparchive.org · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
#standardSQL
# Overall A11Y technology usage by domain rank

WITH a11y_technologies AS (
  SELECT
    _TABLE_SUFFIX AS client,
    url
  FROM
    `httparchive.technologies.2025_06_01_*`
  WHERE
    category = 'Accessibility'
),

pages AS (
  SELECT
    _TABLE_SUFFIX AS client,
    url,
    rank_grouping
  FROM
    `httparchive.summary_pages.2025_06_01_*`,
    UNNEST([1000, 10000, 100000, 1000000, 10000000]) AS rank_grouping
  WHERE
    rank <= rank_grouping
),

rank_totals AS (
  SELECT
    _TABLE_SUFFIX AS client,
    rank_grouping,
    COUNT(0) AS total
  FROM
    `httparchive.summary_pages.2025_06_01_*`,
    UNNEST([1000, 10000, 100000, 1000000, 10000000]) AS rank_grouping
  WHERE
    rank <= rank_grouping
  GROUP BY
    client,
    rank_grouping
)

SELECT
  client,
  rank_grouping AS rank,
  COUNT(DISTINCT url) AS freq,
  total,
  (COUNT(DISTINCT url) / total) * 100 AS pct
FROM
  a11y_technologies
LEFT OUTER JOIN
  pages
USING (client, url)
JOIN
  rank_totals
USING (client, rank_grouping)
GROUP BY
  rank_grouping,
  total,
  client
ORDER BY
  client,
  rank