Yeah, it’s easy to add the form factor dimension:
SELECT
app,
COUNT(DISTINCT origin) AS freq,
IF(form_factor.name = 'desktop', 'desktop', 'mobile') AS form_factor,
ROUND(SUM(IF(bin.start < 1000, bin.density, 0)) / SUM(bin.density), 4) AS fast,
ROUND(SUM(IF(bin.start >= 1000 AND bin.start < 3000, bin.density, 0)) / SUM(bin.density), 4) AS avg,
ROUND(SUM(IF(bin.start >= 3000, bin.density, 0)) / SUM(bin.density), 4) AS slow
FROM
`chrome-ux-report.all.201808`,
UNNEST(first_contentful_paint.histogram.bin) AS bin
JOIN (
SELECT
url,
app
FROM
`httparchive.technologies.2018_08_15_desktop`
WHERE
category = 'CMS'
GROUP BY
url,
app)
ON CONCAT(origin, '/') = url
GROUP BY
app,
form_factor
ORDER BY
freq DESC
