query.sql

-- Trying to figure out if this query is correct
-- and whether there are wasy to optimize it soo it
-- won't take as long as it does to run and produce results
SELECT
  _TABLE_SUFFIX AS yyyymm,
  SUM(fcp.density) AS average_fcp,
  SUM(fp.density) as average_fp,
  SUM(dcl.density) as average_dcl
FROM
  `chrome-ux-report.country_cl.*`, 
  UNNEST(first_paint.histogram.bin) as fp,
  UNNEST(dom_content_loaded.histogram.bin) as dcl,
  UNNEST(first_contentful_paint.histogram.bin) AS fcp
WHERE
  form_factor.name = 'desktop' AND
  fcp.start > 20000
GROUP BY
  yyyymm
ORDER BY
  yyyymm