Gerçek ayak izine bakın, pitch deck'e değil

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-19.jsonl.zst',
       format='newline_delimited', compression='zstd',
       columns={'Technologies':'JSON'})
), total AS (SELECT count(*) AS n FROM t)
SELECT tech, count(*) AS sites,
       round(100.0*count(*)/(SELECT n FROM total), 3) AS pct_of_web
FROM t, unnest(t.techs) AS u(tech)
WHERE tech IN ('Shopify','WooCommerce','Wix','Squarespace','BigCommerce')
GROUP BY tech ORDER BY sites DESC;

SELECT "Country by IP" AS country, count(*) AS sites
FROM read_json('webatla-all-data-2026-08-19.jsonl.zst',
       format='newline_delimited', compression='zstd',
       columns={'Technologies':'JSON', 'Country by IP':'VARCHAR'})
WHERE country IS NOT NULL
  AND list_contains(
        CASE json_type("Technologies")
          WHEN 'ARRAY'  THEN json_extract_string("Technologies",'$[*]')
          WHEN 'OBJECT' THEN json_keys("Technologies")
          ELSE []::VARCHAR[] END, 'Shopify')
GROUP BY country ORDER BY sites DESC LIMIT 15;

# count adoption in each monthly export, then diff
for f in webatla-all-data-2026-06-01 webatla-all-data-2026-07-01; do
  zstdcat "$f.jsonl.zst" |
  jq -r 'def techs: (.Technologies // []) | if type=="object" then keys else . end;
         techs[]' |
  LC_ALL=C sort | uniq -c |
  awk '{c=$1;$1="";sub(/^ /,"");print $0"\t"c}' |
  LC_ALL=C sort -t$'\t' -k1,1 > "$f.tsv"
done
# tech, june, july, net change (biggest gains first)
LC_ALL=C join -t$'\t' webatla-all-data-2026-06-01.tsv webatla-all-data-2026-07-01.tsv |
awk -F$'\t' '{printf "%s\t%d\t%d\t%+d\n",$1,$2,$3,$3-$2}' | sort -t$'\t' -k4 -rn