Query editor
-- ============================================================================-- cbBTC Net Flow Model -- removes same-tx router/pool passthrough inflation-- ============================================================================-- Problem this solves: raw gross transfer sums are massively inflated by-- contracts (DEX routers, aggregators, pools) that receive cbBTC and send it-- back out within the SAME transaction. Validation found a single Base router-- contract (0xbbbb...effcb) generating 86M+ cbBTC of cumulative gross "volume"-- against Base's actual ~38K total cbBTC supply -- a >2000x inflation factor.---- Fix: for every (tx_hash, address) pair, compute the NET amount that address-- gained or lost in that single transaction (sum of legs where it's the-- receiver, minus sum of legs where it's the sender). A pure passthrough-- router receives X and sends X right back out in the same tx -> nets to 0-- and disappears from the flow model, which is the economically correct-- treatment: no real change of ownership occurred for that hop.---- A genuine receiver (e.g. a wallet that receives cbBTC once in a tx and does-- nothing else with it in that same tx) keeps its full net amount. A genuine-- protocol deposit (wallet -> Aave pool, no return leg in that tx) also nets-- correctly: the wallet nets negative, the Aave pool nets positive by the-- full deposit amount -- which is exactly what Section 5 protocol analytics-- needs.---- This does NOT remove genuine multi-hop economic activity across DIFFERENT-- transactions (e.g. deposit now, withdraw next week) -- only same-tx-- round-trips. Multi-hop tracing ACROSS protocols in a single tx (Section 6/7)-- is intentionally deferred; this model only fixes the gross-volume inflation-- problem for Sections 1-3 and 5.-- ============================================================================WITH tx_legs AS (SELECT chain, tx_hash, block_date, block_time, to_address AS addr, amount AS amtFROM query_7838779WHERE flow_type = 'transfer'UNION ALLSELECT chain, tx_hash, block_date, block_time, from_address AS addr, -amount AS amtFROM query_7838779WHERE flow_type = 'transfer'),net_per_tx_address AS (-- collapse to one net amount per (tx, address); keep earliest block_time for the txSELECTchain,tx_hash,MIN(block_date) AS block_date,MIN(block_time) AS block_time,addr,SUM(amt) AS net_amountFROM tx_legsGROUP BY 1, 2, 5HAVING SUM(amt) != 0 -- pure passthroughs net to exactly 0 and drop out here)SELECTchain,tx_hash,block_date,block_time,addr,