cwv_corr_cwv_assessment.sql

SELECT
  COUNT(0) AS urls,
  CORR(lh_perf_score, pct_good_lcp) AS lcp_crux_to_lhscore_corr,
  CORR(lh_perf_score, pct_good_fid) AS fid_crux_to_lhscore_corr,
  CORR(lh_perf_score, pct_good_cls) AS cls_crux_to_lhscore_corr,
  CORR(lh_perf_score, pass_all_cwv) AS cwv_assessment_crux_to_lhscore_corr,
  CORR(pass_all_cwv, pct_good_lcp) AS lcp_crux_to_cwv_assessment_corr,
  CORR(pass_all_cwv, pct_good_fid) AS fid_crux_to_cwv_assessment_corr,
  CORR(pass_all_cwv, pct_good_cls) AS cls_crux_to_cwv_assessment_corr
FROM (
  SELECT
    url,
    CAST(JSON_QUERY(report, "$.categories.performance.score") AS FLOAT64) AS lh_perf_score
  FROM
    `httparchive.lighthouse.2021_05_01_mobile`
)
JOIN (
    select
        url,
        pct_good_lcp,
        pct_good_fid,
        pct_good_cls,
         CAST((pct_good_lcp >= 0.75 AND pct_good_fid >= 0.75 AND pct_good_cls >= 0.75) as INT64) as pass_all_cwv
    from (
    SELECT
        url,
        CAST(JSON_QUERY(payload, "$._CrUX.metrics.largest_contentful_paint.histogram[0].density") AS FLOAT64) AS pct_good_lcp,
        CAST(JSON_QUERY(payload, "$._CrUX.metrics.first_input_delay.histogram[0].density") AS FLOAT64) AS pct_good_fid,
        CAST(JSON_QUERY(payload, "$._CrUX.metrics.cumulative_layout_shift.histogram[0].density") AS FLOAT64) AS pct_good_cls
    FROM
        `httparchive.pages.2021_05_01_mobile`
    )
)
USING
  (url)