Avalanche's April active-user story has three problems

mkdir -p data/avacache/daily
curl -fsSL https://parquet.avacache.com/manifest.json \
  -o data/avacache/manifest.json

jq -r '
  .files[]
  | select(.date >= "2026-04-01" and .date <= "2026-04-30")
  | .key
' data/avacache/manifest.json |
while read -r key; do
  curl -fL "https://parquet.avacache.com/${key}" \
    -o "data/avacache/${key}"
done

python3 -m pip install duckdb

python3 - <<'PY'
import duckdb

TX = "data/avacache/daily/2026-04-*.txs.parquet"
EV = "data/avacache/daily/2026-04-*.events.parquet"

TRANSFER = "0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef"
TRANSFER_SINGLE = "0xc3d58168c5ae7397731d063d5bbf3d15ec90c2ac5a0a42aa7fcb8b37e4df"
TRANSFER_BATCH = "0x4a39dc06d4c0dbc64b70f1f5045c5e13db6db9d9e2e0e28e0f2b2344c8d44e4"
ADDR_TOPIC = "^0x0{24}[0-9a-fA-F]{40}$"
ZERO = "0x0000000000000000000000000000000000000000"

con = duckdb.connect()
query = f"""
WITH
tx AS (
  SELECT
    regexp_extract(filename, '(2026-04-[0-9]{{2}})', 1) AS day,
    lower(from_address) AS from_address,
    lower(to_address) AS to_address
  FROM read_parquet('{TX}', filename=true)
),
ev AS (
  SELECT
    regexp_extract(filename, '(2026-04-[0-9]{{2}})', 1) AS day,
    topic0, topic1, topic2, topic3
  FROM read_parquet('{EV}', filename=true)
),
xfers AS (
  SELECT
    day,
    '0x' || lower(substr(topic1, 27, 40)) AS from_addr,
    '0x' || lower(substr(topic2, 27, 40)) AS to_addr
  FROM ev
  WHERE topic0 = '{TRANSFER}'
    AND regexp_matches(topic1, '{ADDR_TOPIC}')
    AND regexp_matches(topic2, '{ADDR_TOPIC}')

  UNION ALL

  SELECT
    day,
    '0x' || lower(substr(topic2, 27, 40)) AS from_addr,
    '0x' || lower(substr(topic3, 27, 40)) AS to_addr
  FROM ev
  WHERE topic0 IN ('{TRANSFER_SINGLE}', '{TRANSFER_BATCH}')
    AND regexp_matches(topic2, '{ADDR_TOPIC}')
    AND regexp_matches(topic3, '{ADDR_TOPIC}')
),
senders AS (
  SELECT day, from_address AS addr FROM tx
  UNION ALL
  SELECT day, from_addr AS addr FROM xfers
),
active AS (
  SELECT day, from_address AS addr FROM tx
  UNION ALL SELECT day, to_address AS addr FROM tx
  UNION ALL SELECT day, from_addr AS addr FROM xfers
  UNION ALL SELECT day, to_addr AS addr FROM xfers
),
active_counts AS (
  SELECT day, count(DISTINCT addr) AS active_addresses_documented
  FROM active
  WHERE addr IS NOT NULL
    AND addr <> ''
    AND addr <> '{ZERO}'
  GROUP BY day
),
sender_counts AS (
  SELECT day, count(DISTINCT addr) AS active_senders_documented
  FROM senders
  WHERE addr IS NOT NULL
    AND addr <> ''
    AND addr <> '{ZERO}'
  GROUP BY day
)
SELECT
  active_counts.day,
  active_addresses_documented,
  active_senders_documented
FROM active_counts
JOIN sender_counts USING (day)
ORDER BY active_counts.day
"""

print(con.execute(query).fetchdf())
PY

-- After the tx, ev, and xfers CTEs above, add these CTEs and SELECT.
-- The monthly calculation removes the day grouping and deduplicates once
-- across all of April.

, monthly_senders AS (
  SELECT from_address AS addr FROM tx
  UNION ALL
  SELECT from_addr AS addr FROM xfers
),
monthly_active AS (
  SELECT from_address AS addr FROM tx
  UNION ALL SELECT to_address AS addr FROM tx
  UNION ALL SELECT from_addr AS addr FROM xfers
  UNION ALL SELECT to_addr AS addr FROM xfers
)
SELECT
  (SELECT count(DISTINCT addr)
   FROM monthly_active
   WHERE addr IS NOT NULL
     AND addr <> ''
     AND addr <> '0x0000000000000000000000000000000000000000') AS monthly_active_addresses,
  (SELECT count(DISTINCT addr)
   FROM monthly_senders
   WHERE addr IS NOT NULL
     AND addr <> ''
     AND addr <> '0x0000000000000000000000000000000000000000') AS monthly_active_senders;