BigQuery query to running % for CrUX metrics for a given month

CrUX.runningPercentages.sql

#standardSQL

--Update the list of URLs and the date of the tables used

WITH
  origins AS (
  SELECT
    origin
  FROM
    `chrome-ux-report.all.201909`
  WHERE
    origin IN 
    (
        'https://www.amazon.com',
        'https://www.google.com',
        'https://www.realmadrid.com'
    )
  GROUP BY
    origin
  ORDER BY
    origin)
    
    
SELECT
  fp.origin,
  fp.end,
  fp.density AS fpDensity,
  fp.RunningDensityTotal AS fpRunningDensityTotal,
  fcp.density AS fcpDensity,
  fcp.RunningDensityTotal AS fcpRunningDensityTotal,
  dcl.density AS dclDensity,
  dcl.RunningDensityTotal AS dclRunningDensityTotal,
  ol.density AS olDensity,
  ol.RunningDensityTotal AS olRunningDensityTotal
FROM 
(
(

  SELECT
    origin,
    bin.END,
    SUM(SUM(bin.density)) OVER (PARTITION BY origin, bin.END ORDER BY origin, bin.END ) AS density,
    SUM(SUM(bin.density)) OVER (PARTITION BY origin ORDER BY origin, bin.END ) AS RunningDensityTotal
  FROM
    `chrome-ux-report.all.201908`,
    UNNEST(first_paint.histogram.bin) AS bin
  WHERE
    origin IN (
    SELECT
      origin
    FROM
      origins )
  GROUP BY
    origin,
    bin.END
  ORDER BY
    bin.END,
    origin ) fp
  
FULL OUTER JOIN 
(SELECT
    origin,
    bin.END,
    SUM(SUM(bin.density)) OVER (PARTITION BY origin, bin.END ORDER BY origin, bin.END ) AS density,
    SUM(SUM(bin.density)) OVER (PARTITION BY origin ORDER BY origin, bin.END ) AS RunningDensityTotal
  FROM
    `chrome-ux-report.all.201909`,
    UNNEST(first_contentful_paint.histogram.bin) AS bin
  WHERE
    origin IN (
    SELECT
      origin
    FROM
      origins )
  GROUP BY
    origin,
    bin.END
  ORDER BY
    bin.END,
    origin ) fcp
ON
  fcp.origin=fp.origin
  AND fcp.END = fp.END
  FULL OUTER JOIN (
  SELECT
    origin,
    bin.END,
    SUM(SUM(bin.density)) OVER (PARTITION BY origin, bin.END ORDER BY origin, bin.END ) AS density,
    SUM(SUM(bin.density)) OVER (PARTITION BY origin ORDER BY origin, bin.END ) AS RunningDensityTotal
  FROM
    `chrome-ux-report.all.201909`,
    UNNEST(dom_content_loaded.histogram.bin) AS bin
  WHERE
    origin IN (
    SELECT
      origin
    FROM
      origins )
  GROUP BY
    origin,
    bin.END
  ORDER BY
    bin.END,
    origin ) dcl
ON
  fcp.origin=dcl.origin
  AND fcp.END = dcl.END
  
  FULL OUTER JOIN (
  SELECT
    origin,
    bin.END,
    SUM(SUM(bin.density)) OVER (PARTITION BY origin, bin.END ORDER BY origin, bin.END ) AS density,
    SUM(SUM(bin.density)) OVER (PARTITION BY origin ORDER BY origin, bin.END ) AS RunningDensityTotal
  FROM
    `chrome-ux-report.all.201909`,
    UNNEST(onload.histogram.bin) AS bin
  WHERE
    origin IN (
    SELECT
      origin
    FROM
      origins )
  GROUP BY
    origin,
    bin.END
  ORDER BY
    bin.END,
    origin ) ol
ON
  fcp.origin=ol.origin
  AND fcp.END = ol.END
  )
WHERE fp.origin IS NOT NULL
ORDER BY
  fp.end,
  fp.origin