potential-pwas-httparchive

potential-pwas-httparchive.sql

CREATE OR REPLACE TABLE `httparchive-sandbox.test.possible_pwas_mobile` AS
SELECT url, ServiceWorker, manifests FROM
  (
  SELECT
  url,
    IF(JSON_EXTRACT(payload, '$._pwa.serviceWorkerHeuristic') = 'true', 1, 0) AS ServiceWorker,
    IF(JSON_EXTRACT(payload, '$._pwa.manifests') != '[]' AND JSON_EXTRACT(payload, '$._pwa.manifests') != '{}' AND JSON_EXTRACT(payload, '$._pwa.manifests') != '{}', 1, 0) AS manifests
  FROM
    `httparchive.pages.2022_06_01_mobile`
  );

CREATE OR REPLACE TABLE `httparchive-sandbox.test.possible_pwas_desktop` AS
SELECT url, ServiceWorker, manifests FROM
  (
  SELECT
  url,
    IF(JSON_EXTRACT(payload, '$._pwa.serviceWorkerHeuristic') = 'true', 1, 0) AS ServiceWorker,
    IF(JSON_EXTRACT(payload, '$._pwa.manifests') != '[]' AND JSON_EXTRACT(payload, '$._pwa.manifests') != '{}' AND JSON_EXTRACT(payload, '$._pwa.manifests') != '{}', 1, 0) AS manifests
  FROM
    `httparchive.pages.2022_06_01_desktop`
  );
  
SELECT COUNT(*) FROM `httparchive-sandbox.test.possible_pwas_desktop` WHERE ServiceWorker=1 and manifests=1;
SELECT COUNT(*) FROM `httparchive-sandbox.test.possible_pwas_desktop`;

SELECT COUNT(*) FROM `httparchive-sandbox.test.possible_pwas_mobile` WHERE ServiceWorker=1 and manifests=1;
SELECT COUNT(*) FROM `httparchive-sandbox.test.possible_pwas_mobile`;