almanac.httparchive.org/sql/2025/ecommerce/top_payment_provider_by_geo.sql at dc8b05726c712117c627b2eeb0a216f03b049890 · HTTPArchive/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
62
63
64
65
66
67
68
69
#standardSQL
# payment popularity per geo
# top_payment_provider_by_geo.sql
WITH geo_summary AS (
  SELECT
    `chrome-ux-report`.experimental.GET_COUNTRY(country_code) AS geo,
    IF(device = 'desktop', 'desktop', 'mobile') AS client,
    origin,
    COUNT(DISTINCT origin) OVER (PARTITION BY country_code, IF(device = 'desktop', 'desktop', 'mobile')) AS total
  FROM
    `chrome-ux-report.materialized.country_summary`
  WHERE
    yyyymm = 202506
  UNION ALL
  SELECT
    'ALL' AS geo,
    IF(device = 'desktop', 'desktop', 'mobile') AS client,
    origin,
    COUNT(DISTINCT origin) OVER (PARTITION BY IF(device = 'desktop', 'desktop', 'mobile')) AS total
  FROM
    `chrome-ux-report.materialized.device_summary`
  WHERE
    yyyymm = 202506
)

SELECT
  *
FROM (
  SELECT
    client,
    geo,
    payment,
    COUNT(0) AS pages,
    ANY_VALUE(total) AS total,
    COUNT(DISTINCT url) / ANY_VALUE(total) AS pct
  FROM (
    SELECT DISTINCT
      geo,
      client,
      CONCAT(origin, '/') AS url,
      total
    FROM
      geo_summary
  ) JOIN (
    SELECT DISTINCT
      client,
      cats,
      technologies.technology AS payment,
      page AS url
    FROM
      `httparchive.all.pages`,
      UNNEST(technologies) AS technologies,
      UNNEST(technologies.categories) AS cats
    WHERE
      technologies.technology IS NOT NULL AND
      cats = 'Payment processors' AND
      technologies.technology != '' AND
      date = '2025-07-01' AND
      is_root_page
  ) USING (client, url)
  GROUP BY
    client,
    geo,
    payment
)
WHERE
  pages > 1000
ORDER BY
  pages DESC