Skip to content

Instantly share code, notes, and snippets.

@calcsam
Created August 30, 2022 08:19
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save calcsam/6efa869460ffa719392416ce1f89bd1b to your computer and use it in GitHub Desktop.
Save calcsam/6efa869460ffa719392416ce1f89bd1b to your computer and use it in GitHub Desktop.
-- 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