Query editor
WITH usd1_pools_evm AS (-- Get all pools containing USD1 on Ethereum and BNBSELECTblockchain,pool,project,version,token0,token1FROM dex.poolsWHERE blockchain IN ('ethereum', 'bnb')AND (token0 = 0x8d0d000ee44948fc98c9b98a4fa4921476f08b0dOR token1 = 0x8d0d000ee44948fc98c9b98a4fa4921476f08b0d)),recent_trades_evm AS (-- Get recent trades to identify active pools on EVM chainsSELECT DISTINCTt.blockchain,t.project_contract_address as poolFROM dex.trades tINNER JOIN usd1_pools_evm pON t.blockchain = p.blockchainAND t.project_contract_address = p.poolWHERE t.blockchain IN ('ethereum', 'bnb')AND t.block_time >= CURRENT_DATE - INTERVAL '30' DAYAND (t.token_bought_address = 0x8d0d000ee44948fc98c9b98a4fa4921476f08b0dOR t.token_sold_address = 0x8d0d000ee44948fc98c9b98a4fa4921476f08b0d)),pool_liquidity_evm AS (-- Calculate liquidity for each EVM pool from recent tradesSELECTt.blockchain,t.project as dex_name,t.project_contract_address as pool_address,SUM(CASEWHEN t.token_bought_address = 0x8d0d000ee44948fc98c9b98a4fa4921476f08b0dTHEN t.token_bought_amountWHEN t.token_sold_address = 0x8d0d000ee44948fc98c9b98a4fa4921476f08b0dTHEN t.token_sold_amountELSE 0END) as usd1_liquidity,SUM(t.amount_usd) as volume_usdFROM dex.trades tINNER JOIN recent_trades_evm rtON t.blockchain = rt.blockchainAND t.project_contract_address = rt.poolWHERE t.blockchain IN ('ethereum', 'bnb')AND t.block_time >= CURRENT_DATE - INTERVAL '7' DAYAND (t.token_bought_address = 0x8d0d000ee44948fc98c9b98a4fa4921476f08b0dOR t.token_sold_address = 0x8d0d000ee44948fc98c9b98a4fa4921476f08b0d)