displayDensities.sql

CREATE TEMPORARY FUNCTION getImageDensity(payload STRING)
RETURNS ARRAY<STRUCT<clientWidth INT64, approximateResourceWidth INT64, d FLOAT64>>
LANGUAGE js AS '''
try {
  var $ = JSON.parse(payload);
  var responsiveImages = JSON.parse($._responsive_images);
  responsiveImages = responsiveImages['responsive-images'];
  return responsiveImages.map(({approximateResourceWidth, clientWidth}) => ({
    approximateResourceWidth: parseInt(approximateResourceWidth) || 0,
    clientWidth: parseInt(clientWidth) || 0,
    d: (parseInt(clientWidth) > 0 ? parseInt(approximateResourceWidth) / parseInt(clientWidth) : 0),
  }));
} catch (e) {
  return [];
}
''';

SELECT
  percentile,
  client,
  APPROX_QUANTILES(image.d, 1000)[OFFSET(percentile * 10)] AS imgDensity
FROM (
  SELECT
    _TABLE_SUFFIX AS client,
    image
  FROM
    `httparchive.pages.2022_05_01_*`,
    UNNEST(getImageDensity(payload)) AS image),
  UNNEST([10, 25, 50, 75, 90]) AS percentile
WHERE image.approximateResourceWidth > 1 AND image.clientWidth > 0
GROUP BY
  percentile,
  client
ORDER BY
  percentile,
  client

Results

Row	percentile	client	imgDensity
1	10	desktop	0.97883597883597884
2	10	mobile	1.0
3	25	desktop	1.0
4	25	mobile	1.0
5	50	desktop	1.0273972602739727
6	50	mobile	1.6666666666666667
7	75	desktop	1.9031141868512111
8	75	mobile	2.8787878787878789
9	90	desktop	3.5555555555555554
10	90	mobile	5.0

• Mobile crawler was 360w x 512h px @ 3x
• Desktop crawler was 1376w x 768h px @ 1x
  source