Web Almanac 2021 content freshness

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)
);

SELECT
  age,
  COUNT(0) AS n
FROM (
SELECT
  page,
  DATE_DIFF(
    DATE(TIMESTAMP_SECONDS(decode(MAX(encode(PARSE_LAST_MODIFIED(resp_last_modified), startedDateTime))))),
    MAX(PARSE_LAST_MODIFIED(resp_last_modified)),
  DAY) AS age
FROM
  `httparchive.almanac.requests`
WHERE
  date = '2021-07-01' AND
  client = 'desktop' AND
  NET.HOST(page) = NET.HOST(url)
GROUP BY
  page)
GROUP BY
  age
ORDER BY
  age