Query editor
WITH cex_wallets AS (SELECTaddress,CASEWHEN LOWER(cex_name) LIKE '%okx%' OR LOWER(cex_name) LIKE '%okex%' THEN 'OKX'WHEN LOWER(cex_name) LIKE '%binance%' THEN 'Binance'WHEN LOWER(cex_name) LIKE '%bybit%' THEN 'Bybit'END AS exchangeFROM cex.addressesWHERE blockchain = 'ethereum'AND (LOWER(cex_name) LIKE '%okx%'OR LOWER(cex_name) LIKE '%okex%'OR LOWER(cex_name) LIKE '%binance%'OR LOWER(cex_name) LIKE '%bybit%')GROUP BY 1, 2),stables AS (SELECTblock_date AS day,"from" AS sender,"to" AS receiver,amount_usdFROM tokens.transfersWHERE blockchain = 'ethereum'AND block_date >= CURRENT_DATE - INTERVAL '30' DAYAND block_date < CURRENT_DATEAND contract_address IN (0xdac17f958d2ee523a2206206994597c13d831ec7, -- USDT0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48 -- USDC)AND amount_usd IS NOT NULLAND amount_usd > 0),classified AS (SELECTs.day,COALESCE(r.exchange, f.exchange) AS exchange,CASEWHEN r.exchange IS NOT NULL AND f.exchange IS NULL THEN s.amount_usdELSE 0END AS inflow_usd,CASEWHEN f.exchange IS NOT NULL AND r.exchange IS NULL THEN s.amount_usdELSE 0END AS outflow_usdFROM stables sLEFT JOIN cex_wallets r ON s.receiver = r.addressLEFT JOIN cex_wallets f ON s.sender = f.addressWHERE (r.exchange IS NOT NULL AND f.exchange IS NULL)OR (f.exchange IS NOT NULL AND r.exchange IS NULL))SELECTday,exchange,ROUND(SUM(inflow_usd) / 1e6, 2) AS inflow_m_usd,ROUND(SUM(outflow_usd) / 1e6, 2) AS outflow_m_usd,ROUND((SUM(inflow_usd) - SUM(outflow_usd)) / 1e6, 2) AS netflow_m_usdFROM classified