Query editor
-- ============================================================================-- cbBTC Address Classification: Infrastructure vs Economic Endpoint-- ============================================================================-- Problem: same-tx netting (query_7838829) fixed same-transaction round-trips-- but left a long tail of router/pool contracts that cycle float across many-- SEPARATE transactions. A pool that does 50,000 individual swap transactions,-- each individually "real" at the per-tx level, is still not an economic-- holder -- it's infrastructure with persistent float, and including it in-- chain-level net inflow/outflow inflates numbers far past actual supply.---- Fix: classify every address by FULL-WINDOW behavioral signature rather than-- per-transaction signature.---- infra_score signal 1 (turnover_ratio): gross_volume / max(abs(net_position), 1 unit)-- -- a wallet that receives 10 cbBTC and holds it has turnover_ratio ~1.-- -- a router that cycles 50,000 cbBTC through while holding ~0 net has-- turnover_ratio in the thousands or more.---- infra_score signal 2 (legs_per_tx): already computed in query_7838822,-- ~1.0-1.1 indicates the address appears once per tx in a simple pass--- through pattern (receive-then-send within the tx, or alternating across-- txs), vs a genuine multi-leg participant.---- Classification rule (transparent, single tunable threshold):-- is_infrastructure = true WHEN is_smart_contract = true-- AND turnover_ratio >= 50 -- moved 50x+ its-- net resting position during the window-- AND n_distinct_txs >= 20 -- excludes one-off-- large single-tx transfers from being misflagged---- This threshold (50x turnover) is a SINGLE documented knob. If validation-- against known circulating supply still shows inflation after this pass,-- the fix is to LOWER this threshold here, not to add more manual exclusions-- -- keeping this systematic instead of a hand-maintained denylist.-- ============================================================================WITH legs AS (SELECT chain, from_address AS addr, tx_hash, -amount AS signed_amt FROM query_7838779 WHERE flow_type = 'transfer'UNION ALLSELECT chain, to_address AS addr, tx_hash, amount AS signed_amt FROM query_7838779 WHERE flow_type = 'transfer'),addr_window_stats AS (SELECTchain,addr,COUNT(*) AS n_legs,COUNT(DISTINCT tx_hash) AS n_distinct_txs,SUM(ABS(signed_amt)) AS gross_volume,SUM(signed_amt) AS net_positionFROM legsGROUP BY 1, 2HAVING COUNT(*) >= 10 -- below this, turnover ratio is noisy and irrelevant to chain totals anyway),scored AS (SELECTs.chain,s.addr,s.n_legs,