HTTP Archive query comparing Server-Timing `wp-total` (from Performance Lab plugin) with Time to First Byte

wp-total-compared-with-ttfb.sql

CREATE TEMPORARY FUNCTION GET_SERVER_TIMING_HEADER(headers ARRAY<STRUCT<name STRING, value STRING>>) RETURNS STRING AS (
  IFNULL((
    SELECT
      value
    FROM
      UNNEST(headers) AS header
    WHERE
      LOWER(header.name) = 'server-timing'
    LIMIT 1
  ), '')
);

WITH serverTimings AS (
  SELECT
    date,
    IF(client = "mobile", "phone", "desktop") AS device,
    page AS url,
    httparchive.fn.PARSE_SERVER_TIMING_HEADER(GET_SERVER_TIMING_HEADER(response_headers)) AS serverTiming,
    JSON_EXTRACT(lighthouse, "$.audits.server-response-time.numericValue") AS lighthouseTtfb
  FROM
    `httparchive.all.pages`,
    UNNEST(technologies) AS technology
  INNER JOIN
    `httparchive.all.requests`
  USING
    (date, client, page, is_root_page)
  WHERE
    date = CAST("2023-10-01" AS DATE)
    AND technology.technology = "Performance Lab"
    AND is_main_document = TRUE
)

SELECT
  date,
  device,
  url,
  CAST(serverTimingRecord.dur AS FLOAT64) AS wpTotal,
  CAST(lighthouseTtfb AS FLOAT64) AS ttfb,
  CAST(serverTimingRecord.dur AS FLOAT64) / CAST(lighthouseTtfb AS FLOAT64) AS pctWpTotalOfTtfb
FROM
  serverTimings,
  UNNEST(serverTiming) AS serverTimingRecord
WHERE
  serverTimingRecord.name = 'wp-total'
  AND lighthouseTtfb IS NOT NULL
ORDER BY
  device,
  url