almanac.httparchive.org/sql/2021/media/image_sizes_attribute_strings.sql at main · HTTPArchive/almanac.httparchive.org · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT
  client,
  sizes,
  COUNT(0) AS freq,
  SUM(COUNT(0)) OVER (PARTITION BY 0) AS total,
  COUNT(0) / SUM(COUNT(0)) OVER (PARTITION BY 0) AS pct
FROM
  `httparchive.almanac.summary_response_bodies`,
  UNNEST(REGEXP_EXTRACT_ALL(body, r'(?im)<(?:source|img)[^>]*sizes=[\'"]?([^\'"]*)')) AS sizes
WHERE
  date = '2021-07-01' AND
  firstHtml
GROUP BY
  client,
  sizes
ORDER BY
  freq DESC
LIMIT
  100