Query editor
-- ============================================================================-- cbBTC Economic Net Flow Model-- ============================================================================-- Combines:-- 1. Same-tx netting (query_7838829 logic, re-derived here directly from the-- base flow model query_7838779 for a single clean dependency chain)-- 2. Infrastructure exclusion (query_7839052) -- addresses classified as-- routers/pools/aggregators by turnover_ratio >= 50 and n_distinct_txs-- >= 20 are excluded from economic net flow entirely.---- Output grain: one row per (chain, tx_hash, address) net change, restricted-- to economic endpoints. This is what Sections 1-3 and 5 aggregate further.-- ============================================================================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 (SELECTchain,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)SELECTn.chain,n.tx_hash,n.block_date,n.block_time,n.addr,n.net_amount,CASE WHEN n.net_amount > 0 THEN 'net_inflow' ELSE 'net_outflow' END AS directionFROM net_per_tx_address nLEFT JOIN query_7839052 cON c.chain = n.chain AND c.addr = n.addrWHERE COALESCE(c.is_infrastructure, false) = false