Query editor
-- ============================================================================-- cbBTC Router / Passthrough Contract Detection-- ============================================================================-- Purpose: identify contracts that are most likely acting as ROUTERS, DEX-- pools, or aggregator infrastructure rather than genuine economic holders.-- These addresses generate enormous gross transfer volume relative to actual-- net cbBTC they ever hold, because tokens pass through them within the same-- transaction (e.g. swap_in -> route -> swap_out).---- Why this matters: Section 2 (chain inflow/outflow) and Section 5 (protocol-- analytics) need GROSS and NET flow numbers that mean something economically.-- If we sum every leg involving these contracts, both gross inflow AND gross-- outflow numbers balloon to numbers far above actual circulating supply,-- making the dashboard useless (confirmed empirically: a single Base contract-- showed 86M+ cbBTC of cumulative "volume" against a ~38K total Base supply).---- Detection heuristic (tunable):-- - n_legs (transfer legs touching the address) is very high-- - AND legs_per_counterparty is high (same handful of counterparties-- transacted with repeatedly -- characteristic of a pool/router, not an-- organic holder with diverse counterparties)-- - is_smart_contract = true (heuristic doesn't apply to EOAs)---- This is a CANDIDATE list for human review, not an automatic blacklist.-- A real lending pool (e.g. Aave's aToken contract) will ALSO show high leg-- counts and should stay IN the protocol analytics layer (Section 5) -- it-- should only be excluded from "wallet-level net flow" framing, not from-- protocol-level analytics. Cross-reference against the protocol labels-- table (query_7838775) before excluding anything from Section 5.-- ============================================================================WITH legs AS (SELECT chain, from_address AS addr, tx_hash, amount FROM query_7838779UNION ALLSELECT chain, to_address AS addr, tx_hash, amount FROM query_7838779),addr_stats AS (SELECTchain,addr,COUNT(*) AS n_legs,COUNT(DISTINCT tx_hash) AS n_distinct_txs,SUM(amount) AS gross_volumeFROM legsGROUP BY 1, 2HAVING COUNT(*) >= 500 -- filter out long tail before the expensive contract-check join)SELECTs.chain,s.addr,s.n_legs,s.n_distinct_txs,s.gross_volume,ROUND(s.n_legs * 1.0 / s.n_distinct_txs, 2) AS legs_per_tx,a.is_smart_contract,a.first_deployment_dateFROM addr_stats sLEFT JOIN addresses.stats a