-
-
Save calcsam/6efa869460ffa719392416ce1f89bd1b to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- This query was run six times, with 08_01 replaced with 07_01, 06_01, etc | |
WITH websites AS ( | |
select | |
technologies.url, | |
ANY_VALUE(technologies.app) as framework, | |
From `httparchive.technologies.2022_08_01_mobile` technologies | |
WHERE technologies.app in ('Next.js', 'Nuxt.js', 'Gatsby' ) -- 'Express', 'Nginx', 'Apache' | |
AND technologies.url is not null | |
GROUP BY technologies.url | |
) , raw_site_metrics as ( | |
SELECT lh.url, | |
framework, | |
cast(json_extract(lh.report, "$.categories['performance'].score") as FLOAT64) as performance, | |
cast(json_extract(lh.report, "$.categories['accessibility'].score") as FLOAT64) as accessibility, | |
cast(json_extract(lh.report, "$.categories['best-practices'].score") as FLOAT64) as best_practices, | |
cast(json_extract(lh.report, "$.categories['seo'].score") as FLOAT64) as seo, | |
cast(json_extract(lh.report, "$.categories['pwa'].score") as FLOAT64) as pwa, | |
CAST(JSON_EXTRACT_SCALAR(crux_mobile.payload, "$['_TTFB']") AS NUMERIC) AS mobile_ttfb, | |
CAST(JSON_EXTRACT_SCALAR(crux_mobile.payload, "$['_chromeUserTiming.firstContentfulPaint']") AS NUMERIC) AS mobile_fcp, | |
CAST(JSON_EXTRACT_SCALAR(crux_mobile.payload, "$['_chromeUserTiming.LargestTextPaint']") AS NUMERIC) AS mobile_ltp, | |
CAST(JSON_EXTRACT_SCALAR(crux_mobile.payload, "$['_chromeUserTiming.LargestContentfulPaint']") AS NUMERIC) AS mobile_lcp, | |
CAST(JSON_EXTRACT_SCALAR(crux_mobile.payload, "$['_visualComplete85']") AS NUMERIC) AS mobile_visual_complete_85, | |
CAST(JSON_EXTRACT_SCALAR(crux_mobile.payload, "$['_TimeToInteractive']") AS NUMERIC) AS mobile_tti, | |
CAST(JSON_EXTRACT_SCALAR(crux_mobile.payload, "$['_visualComplete']") AS NUMERIC) AS mobile_visual_complete_100, | |
CAST(JSON_EXTRACT_SCALAR(crux_mobile.payload, "$['_TotalBlockingTime']") AS NUMERIC) AS mobile_tbt, | |
CAST(JSON_EXTRACT_SCALAR(crux_mobile.payload, "$['_maxFID']") AS NUMERIC) AS mobile_fid, | |
CAST(JSON_EXTRACT_SCALAR(crux_desktop.payload, "$['_TTFB']") AS NUMERIC) AS desktop_ttfb, | |
CAST(JSON_EXTRACT_SCALAR(crux_desktop.payload, "$['_chromeUserTiming.firstContentfulPaint']") AS NUMERIC) AS desktop_fcp, | |
CAST(JSON_EXTRACT_SCALAR(crux_desktop.payload, "$['_chromeUserTiming.LargestTextPaint']") AS NUMERIC) AS desktop_ltp, | |
CAST(JSON_EXTRACT_SCALAR(crux_desktop.payload, "$['_chromeUserTiming.LargestContentfulPaint']") AS NUMERIC) AS desktop_lcp, | |
CAST(JSON_EXTRACT_SCALAR(crux_desktop.payload, "$['_visualComplete85']") AS NUMERIC) AS desktop_visual_complete_85, | |
CAST(JSON_EXTRACT_SCALAR(crux_desktop.payload, "$['_TimeToInteractive']") AS NUMERIC) AS desktop_tti, | |
CAST(JSON_EXTRACT_SCALAR(crux_desktop.payload, "$['_visualComplete']") AS NUMERIC) AS desktop_visual_complete_100, | |
CAST(JSON_EXTRACT_SCALAR(crux_desktop.payload, "$['_TotalBlockingTime']") AS NUMERIC) AS desktop_tbt, | |
CAST(JSON_EXTRACT_SCALAR(crux_desktop.payload, "$['_maxFID']") AS NUMERIC) AS desktop_fid, | |
FROM websites | |
LEFT JOIN ( -- guard against duplicate url values | |
SELECT url, ANY_VALUE(report) as report | |
FROM `httparchive.lighthouse.2022_08_01_mobile` | |
GROUP BY url | |
) lh ON websites.url = lh.url | |
LEFT JOIN ( -- original table contains duplicate url values | |
SELECT url, ANY_VALUE(payload) as payload | |
FROM `httparchive.pages.2022_08_01_mobile` | |
GROUP BY url | |
) crux_mobile ON crux_mobile.url = websites.url | |
LEFT JOIN ( -- original table contains duplicate url values | |
SELECT url, ANY_VALUE(payload) as payload | |
FROM `httparchive.pages.2022_08_01_desktop` | |
GROUP BY url | |
) crux_desktop ON crux_desktop.url = websites.url | |
WHERE websites.url != "" | |
) | |
SELECT | |
framework, | |
-- use of any_value due to BigQuery not supporting percentile_cont as an aggregation function | |
-- see https://stackoverflow.com/questions/45579692/percentile-functions-with-groupby-in-bigquery | |
COUNT(*) as num_sites, | |
AVG(performance) as lighthouse_score, | |
AVG(mobile_ttfb) as mobile_time_to_first_byte, | |
AVG(mobile_fcp) as mobile_first_contentful_paint, | |
AVG(mobile_ltp) as mobile_largest_text_paint, | |
AVG(mobile_lcp) as mobile_largest_contentful_paint, | |
AVG(mobile_visual_complete_85) as mobile_visual_complete_85, | |
AVG(mobile_tti) as mobile_time_to_interactive, | |
AVG(mobile_visual_complete_100) as mobile_visual_complete_100, | |
AVG(mobile_tbt) as mobile_total_blocking_time, | |
AVG(mobile_fid) as mobile_first_input_delay, | |
AVG(desktop_ttfb) as desktop_time_to_first_byte, | |
AVG(desktop_fcp) as desktop_first_contentful_paint, | |
AVG(desktop_ltp) as desktop_largest_text_paint, | |
AVG(desktop_lcp) as desktop_largest_contentful_paint, | |
AVG(desktop_visual_complete_85) as desktop_visual_complete_85, | |
AVG(desktop_visual_complete_100) as desktop_visual_complete_100, | |
AVG(desktop_tti) as desktop_time_to_interactive, | |
AVG(desktop_tbt) as desktop_total_blocking_time, | |
AVG(desktop_fid) as desktop_first_input_delay, | |
FROM raw_site_metrics | |
GROUP BY framework |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment