near-toll-audit / pipeline/sql/07_new_account_funders.sql · raw
-- Who funds new accounts: the first NEAR transfer or create-account receipt into an account seen in the window.-- Sampled to two months (before and after the April 2026 break) because receipt_actions is costly to scan.WITH e AS (  SELECT block_date, block_height, receipt_receiver_account_id AS acct, receipt_predecessor_account_id AS funder  FROM `bigquery-public-data.crypto_near_mainnet_us.receipt_actions`  WHERE (block_date BETWEEN '2026-03-01' AND '2026-03-31' OR block_date BETWEEN '2026-10-01' AND '2026-10-31')    AND action_kind IN ('CREATE_ACCOUNT', 'TRANSFER')    AND receipt_predecessor_account_id != 'system'    AND receipt_predecessor_account_id != receipt_receiver_account_id), first_seen AS (  SELECT acct, DATE_TRUNC(block_date, MONTH) AS m, ARRAY_AGG(funder ORDER BY block_height LIMIT 1)[OFFSET(0)] AS funder  FROM e GROUP BY 1, 2), s AS (  SELECT DISTINCT signer_account_id AS acct, DATE_TRUNC(block_date, MONTH) AS m  FROM `bigquery-public-data.crypto_near_mainnet_us.transactions`  WHERE block_date BETWEEN '2026-03-01' AND '2026-03-31' OR block_date BETWEEN '2026-10-01' AND '2026-10-31'), newly AS (  SELECT f.m, f.acct, f.funder, s.acct IS NOT NULL AS signed_same_month  FROM first_seen f LEFT JOIN s USING (acct, m))SELECT m, funder, COUNT(*) AS accounts_touched, COUNTIF(signed_same_month) AS accounts_that_signedFROM newly GROUP BY 1, 2QUALIFY ROW_NUMBER() OVER (PARTITION BY m ORDER BY accounts_touched DESC) <= 300 OR accounts_touched >= 2ORDER BY 1, 3 DESC