Un recensement de l’internet, avec un budget recherche

WITH t AS (
  SELECT CASE json_type("Technologies")
           WHEN 'ARRAY'  THEN json_extract_string("Technologies", '$[*]')
           WHEN 'OBJECT' THEN json_keys("Technologies")
           ELSE []::VARCHAR[] END AS techs
  FROM read_json('webatla-all-data-2026-08-17.jsonl.zst',
       format='newline_delimited', compression='zstd',
       columns={'Technologies':'JSON'})
)
SELECT tech, count(*) AS sites
FROM t, unnest(t.techs) AS u(tech)
GROUP BY tech ORDER BY sites DESC LIMIT 25;

SELECT "Country by IP" AS country, count(*) AS domains
FROM read_json('webatla-all-data-2026-08-17.jsonl.zst',
       format='newline_delimited', compression='zstd',
       columns={'Country by IP':'VARCHAR'})
WHERE country IS NOT NULL
GROUP BY country ORDER BY domains DESC;

SELECT "DNS Status" AS resolves, count(*) AS n,
       round(100.0*count(*)/sum(count(*)) OVER (), 2) AS pct
FROM read_json('webatla-all-data-2026-08-17.jsonl.zst',
       format='newline_delimited', compression='zstd',
       columns={'DNS Status':'VARCHAR'})
GROUP BY resolves ORDER BY n DESC;