CrUX: Export all metrics per Origin and Device

# Warning: This Query will process 57GB approx, consider BigQuery API costs.

WITH dates AS
(SELECT
  yyyymm
  FROM
  `chrome-ux-report.materialized.device_summary`
GROUP BY yyyymm
ORDER BY yyyymm DESC
LIMIT 13)

#standardSQL
SELECT
  yyyymm as `Date`,
  origin,
  device,
  p75_ttfb,
  p75_fcp,
  p75_lcp,
  p75_fid,
  p75_cls,
  ROUND(IF(fast_fcp>0, fast_fcp / (fast_fcp + avg_fcp + slow_fcp), 0),2) AS fast_fcp,
  ROUND(IF(avg_fcp>0, avg_fcp / (fast_fcp + avg_fcp + slow_fcp), 0),2) AS avg_fcp,
  ROUND(IF(slow_fcp>0, slow_fcp / (fast_fcp + avg_fcp + slow_fcp), 0),2) AS slow_fcp,
  ROUND(IF(fast_lcp>0, fast_lcp / (fast_lcp + avg_lcp + slow_lcp), 0),2) AS fast_lcp,
  ROUND(IF(avg_lcp>0,  avg_lcp  / (fast_lcp + avg_lcp + slow_lcp), 0),2) AS avg_lcp,
  ROUND(IF(slow_lcp>0, slow_lcp / (fast_lcp + avg_lcp + slow_lcp), 0),2) AS slow_lcp,
  ROUND(IF(fast_fid>0, fast_fid / (fast_fid + avg_fid + slow_fid), 0),2) AS fast_fid,
  ROUND(IF(avg_fid>0, avg_fid / (fast_fid + avg_fid + slow_fid), 0),2) AS avg_fid,
  ROUND(IF(slow_fid>0, slow_fid / (fast_fid + avg_fid + slow_fid), 0),2) AS slow_fid,
  ROUND(IF(fast_ttfb>0,  fast_ttfb / (fast_ttfb + avg_ttfb + slow_ttfb), 0),2) AS fast_ttfb,
  ROUND(IF(avg_ttfb>0,  avg_ttfb / (fast_ttfb + avg_ttfb + slow_ttfb), 0),2) AS avg_ttfb,
  ROUND(IF(slow_ttfb>0,  slow_ttfb / (fast_ttfb + avg_ttfb + slow_ttfb), 0),2) AS slow_ttfb,
  ROUND(IF(small_cls>0, small_cls / (small_cls + medium_cls + large_cls), 0),2) AS small_cls,
  ROUND(IF(medium_cls>0, medium_cls / (small_cls + medium_cls + large_cls), 0),2) AS medium_cls,
  ROUND(IF(large_cls>0, large_cls / (small_cls + medium_cls + large_cls), 0),2) AS large_cls,
  ROUND(IF(notification_permission_accept>0, notification_permission_accept/(notification_permission_accept+notification_permission_deny+notification_permission_ignore+notification_permission_dismiss), 0),2) as notification_permission_accept,
  ROUND(IF(notification_permission_deny>0, notification_permission_deny/(notification_permission_accept+notification_permission_deny+notification_permission_ignore+notification_permission_dismiss), 0),2) as notification_permission_deny,
  ROUND(IF(notification_permission_ignore>0, notification_permission_ignore/(notification_permission_accept+notification_permission_deny+notification_permission_ignore+notification_permission_dismiss), 0),2) as notification_permission_ignore,
  ROUND(IF(notification_permission_dismiss>0, notification_permission_dismiss/(notification_permission_accept+notification_permission_deny+notification_permission_ignore+notification_permission_dismiss), 0),2) as notification_permission_dismiss,
  ROUND(IF(_4GDensity>0, _4GDensity/(_4GDensity+_3GDensity+_2GDensity+slow2GDensity+offlineDensity),0),2) as _4GDensity,
  ROUND(IF(_3GDensity>0, _3GDensity/(_4GDensity+_3GDensity+_2GDensity+slow2GDensity+offlineDensity),0),2) as _3GDensity,
  ROUND(IF(_2GDensity>0, _2GDensity/(_4GDensity+_3GDensity+_2GDensity+slow2GDensity+offlineDensity),0),2) as _2GDensity,
  ROUND(IF(slow2GDensity>0, slow2GDensity/(_4GDensity+_3GDensity+_2GDensity+slow2GDensity+offlineDensity),0),2) as slow2GDensity,
  ROUND(IF(offlineDensity>0, offlineDensity/(_4GDensity+_3GDensity+_2GDensity+slow2GDensity+offlineDensity),0),2) as offlineDensity,
FROM
  `chrome-ux-report.materialized.device_summary`
WHERE
  origin IN (
    "https://web.dev"
  )
AND
  yyyymm IN (SELECT yyyymm FROM dates)
AND
 device in ("desktop", "phone")
ORDER BY origin, yyyymm;