Query editor
-- ============================================================================-- Section 5: Protocol Analytics-- ============================================================================-- For every labeled protocol (and 'other' buckets for unlabeled contracts and-- wallets), shows gross inflow, outflow, net flow, net balance held, tx count,-- unique counterparty count, and chain supply share.---- Methodology:-- A protocol's "inflow" = sum of net_amount where the protocol address is the-- net receiver in a tx (net_amount > 0 at that address after same-tx netting).-- A protocol's "outflow" = sum of abs(net_amount) where it's the net sender.-- Net position (proxy for balance) = cumulative sum of signed net_amount over-- the 35-day window -- NOT the same as actual on-chain balance since this-- only covers the rolling window, not all-time history. True balance requires-- an all-time scan; flagged as a known gap.---- Only EVM chains are in scope here (Ethereum, Base, Arbitrum). Solana protocol-- labeling requires a separate pass against Solana program IDs, which are-- structurally different (programs vs. pool contract addresses).-- ============================================================================WITH evm_contracts AS (SELECT contract_address, chain FROM (VALUES('0xcbb7c0000ab88b473b1f5afd9ef808440eed33bf', 'ethereum'),('0xcbb7c0000ab88b473b1f5afd9ef808440eed33bf', 'base'),('0xcbb7c0000ab88b473b1f5afd9ef808440eed33bf', 'arbitrum'),('0xcbb7c0000ab88b473b1f5afd9ef808440eed33bf', 'monad')) AS t(contract_address, chain)),evm_supply AS (SELECT t.blockchain AS chain,SUM(CASE WHEN t."from" = 0x0000000000000000000000000000000000000000 THEN t.amount ELSE 0 END) -SUM(CASE WHEN t."to" = 0x0000000000000000000000000000000000000000 THEN t.amount ELSE 0 END) AS circulating_supplyFROM tokens.transfers tINNER JOIN evm_contracts c ON t.blockchain = c.chainAND t.contract_address = from_hex(c.contract_address)WHERE (t."from" = 0x0000000000000000000000000000000000000000OR t."to" = 0x0000000000000000000000000000000000000000)GROUP BY 1),raw AS (SELECT t.blockchain AS chain, CAST(t.tx_hash AS varchar) AS tx_hash,t.block_date,CAST(t."from" AS varchar) AS from_address,CAST(t."to" AS varchar) AS to_address,t.amountFROM tokens.transfers tINNER JOIN evm_contracts c ON t.blockchain = c.chainAND t.contract_address = from_hex(c.contract_address)WHERE t.block_date >= CURRENT_DATE - INTERVAL '35' DAYAND t."from" != 0x0000000000000000000000000000000000000000AND t."to" != 0x0000000000000000000000000000000000000000),legs AS (SELECT chain, tx_hash, block_date, to_address AS addr, amount AS signed_amt FROM rawUNION ALLSELECT chain, tx_hash, block_date, from_address AS addr, -amount AS signed_amt FROM raw