Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment