Skip to content

Instantly share code, notes, and snippets.

Avatar

Rick Viscomi rviscomi

View GitHub Profile
View cms-adoption-share.sql
WITH app_list AS (
SELECT DISTINCT
app
FROM
`httparchive.technologies.2022_05_01_mobile`
WHERE
category IN ('CMS')
), category AS (
SELECT
_TABLE_SUFFIX,
View 220512_Dx0_11Ll0.har
{
"pageref": "page_1_0_1",
"_run": 1,
"_cached": 0,
"startedDateTime": "2022-05-13T16:17:30.615041",
"time": 623,
"request": {
"method": "GET",
"url": "https://m.twfanti.com/search.html",
"headersSize": 672,
@rviscomi
rviscomi / getIfConditionals.sql
Last active Mar 23, 2022
Analysis of the top SCSS `if` conditionals using HTTP Archive data from March 2022 (mobile). See the full results at https://docs.google.com/spreadsheets/d/1ZMoqLRu2OpBDi-kLgJdTzAAkZwPeHe5sLHjTjt5vEng/edit?usp=sharing and https://github.com/w3c/csswg-drafts/issues/6684#issuecomment-1076543094 for more context.
View getIfConditionals.sql
CREATE TEMPORARY FUNCTION getIfConditionals(payload STRING) RETURNS
ARRAY<STRING> LANGUAGE js AS '''
try {
var $ = JSON.parse(payload);
var sass = JSON.parse($['_sass']);
return sass.scss.stats.ifs.map(i => i.test);
} catch (e) {
return [];
}
@rviscomi
rviscomi / fresh.sql
Created Dec 1, 2021
Web Almanac 2021 content freshness
View fresh.sql
CREATE TEMP FUNCTION PARSE_LAST_MODIFIED(last_modified STRING) RETURNS DATE DETERMINISTIC AS (
CAST(SAFE.PARSE_DATETIME('%a, %d %h %Y %T GMT', last_modified) AS DATE)
);
CREATE TEMP FUNCTION encode(comparator DATE, data INT64) RETURNS STRING DETERMINISTIC AS (
CONCAT(CAST(comparator AS STRING), CAST(data AS STRING))
);
CREATE TEMP FUNCTION decode(value STRING) RETURNS INT64 DETERMINISTIC AS (
CAST(SUBSTR(value, 11) AS INT64)
View summary_requests.2021_10_01_mobile.sql
CREATE TEMPORARY FUNCTION getSummary(url STRING, payload STRING)
RETURNS STRUCT<requestId INT64, pageid INT64, startedDateTime INT64, time INT64, method STRING, url STRING, urlShort STRING, redirectUrl STRING, firstReq BOOLEAN, firstHtml BOOLEAN, reqHttpVersion STRING, reqHeadersSize INT64,
reqBodySize INT64, reqCookieLen INT64, reqOtherHeaders STRING, status INT64, respHttpVersion STRING, respHeadersSize INT64, respBodySize INT64, respSize INT64, respCookieLen INT64, expAge INT64, mimeType STRING, respOtherHeaders STRING,
req_accept STRING, req_accept_charset STRING, req_accept_encoding STRING, req_accept_language STRING, req_connection STRING, req_host STRING, req_if_modified_since STRING, req_if_none_match STRING, req_referer STRING, req_user_agent STRING,
resp_accept_ranges STRING, resp_age STRING, resp_cache_control STRING, resp_connection STRING, resp_content_encoding STRING, resp_content_language STRING, resp_content_length STRING, resp_content_location STRING, resp_content_type STRING,
resp_da
@rviscomi
rviscomi / cms-cwv.sql
Created Aug 19, 2021
August 19, 2021 Web Almanac live stream queries
View cms-cwv.sql
SELECT
client,
app,
origins,
SAFE_DIVIDE(origins_with_good_cwv, origins_eligible_for_cwv) AS pct_good_cwv
FROM
`httparchive.core_web_vitals.technologies`
WHERE
date = "2021-07-01" AND
REGEXP_CONTAINS(categories, r'CMS')
@rviscomi
rviscomi / lazy-loading-crux-lcp-wordpress.sql
Last active Aug 30, 2021
Exploration into the correlation between native image lazy loading and LCP performance.
View lazy-loading-crux-lcp-wordpress.sql
# Distribution of LCP performance on WordPress pages across desktop/mobile with and without native image lazy loading.
SELECT
_TABLE_SUFFIX AS client,
percentile,
usesLazyLoading,
COUNT(0) AS pages,
COUNTIF(pctGoodLCP IS NOT NULL) AS pagesWithCrUXData,
APPROX_QUANTILES(pctGoodLCP, 1000)[OFFSET(percentile * 10)] AS pctGoodLCP,
APPROX_QUANTILES(p75LCP, 1000)[OFFSET(percentile * 10)] AS p75LCP
FROM (
@rviscomi
rviscomi / mdn-content-popularity-tracker.gs
Last active May 28, 2021
Apps Script code for syncing MDN content popularity stats to Google Sheets to be visualized in the content dashboard: bit.ly/mdn-content-dash
View mdn-content-popularity-tracker.gs
// Written by Rick Viscomi (@rick_Viscomi)
const MDN_JSON = 'https://raw.githubusercontent.com/mdn/content/main/files/popularities.json';
const MDN_JSON_COMMITS = 'https://api.github.com/repos/mdn/content/commits?path=files/popularities.json';
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Raw data')
function run() {
const lastCommit = getLastCommit();
const lastCommitDate = new Date(lastCommit.author.date).toLocaleDateString();
const lastEntryDate = new Date(sheet.getRange(sheet.getLastRow(), 1).getValue()).toLocaleDateString();
@rviscomi
rviscomi / web-app-manifest.js
Created May 12, 2021
WebPageTest custom metric: web app manifest
View web-app-manifest.js
[web-app-manifest]
const response_bodies = $WPT_BODIES;
const manifestURLs = new Set(Array.from(document.querySelectorAll('link[rel=manifest]')).map(link => {
const base = new URL(location.href).origin;
const href = link.getAttribute('href');
return new URL(href, base).href;
}));
const manifests = response_bodies.filter(har => {
@rviscomi
rviscomi / 0. state-of-the-web-httparchive-10th-anniversary.md
Last active Nov 19, 2020
Show notes for the 10th Anniversary of HTTP Archive episode of the State of the Web podcast
View 0. state-of-the-web-httparchive-10th-anniversary.md

HTTP Archive's 10th Anniversary - The State of the Web

Published November 19, 2020

Rick meets with Steve Souders, who created the HTTP Archive project 10 years ago this month, to talk about its origins and reflect on it's growth. They're also joined by Patrick Meenan, creator of WebPageTest and maintainer of HTTP Archive, along with Paul Calvano, past State of the Web guest and also a maintainer of HTTP Archive.

Links to resources discussed in this episode: