Query editor
WITH base AS (SELECTCAST(block_date AS DATE) AS day,bot,CASEWHEN token_bought_symbol IN ('ETH','WETH') OR token_sold_symbol IN ('ETH','WETH') THEN 'ETH'WHEN token_bought_symbol IN ('BTC','WBTC','CBBTC','TBTC') OR token_sold_symbol IN ('BTC','WBTC','CBBTC','TBTC') THEN 'BTC'END AS asset,amount_usd,CASEWHEN token_bought_symbol IN ('ETH','WETH','BTC','WBTC','CBBTC','TBTC') THEN amount_usdWHEN token_sold_symbol IN ('ETH','WETH','BTC','WBTC','CBBTC','TBTC') THEN -amount_usdELSE 0END AS signed_usdFROM dex_evm.bot_tradesWHERE block_month >= date_trunc('month', CURRENT_DATE - INTERVAL '120' DAY)AND CAST(block_date AS DATE) >= CURRENT_DATE - INTERVAL '120' DAYAND amount_usd IS NOT NULLAND (token_bought_symbol IN ('ETH','WETH','BTC','WBTC','CBBTC','TBTC')OR token_sold_symbol IN ('ETH','WETH','BTC','WBTC','CBBTC','TBTC'))),daily AS (SELECTday,asset,COUNT(DISTINCT bot) AS active_bots,SUM(amount_usd) AS bot_gross_usd,SUM(signed_usd) AS bot_net_usdFROM baseWHERE asset IS NOT NULLGROUP BY 1,2)SELECTday,SUM(CASE WHEN asset='ETH' THEN active_bots ELSE 0 END) AS eth_active_bots,SUM(CASE WHEN asset='BTC' THEN active_bots ELSE 0 END) AS btc_active_bots,SUM(CASE WHEN asset='ETH' THEN bot_gross_usd ELSE 0 END) AS eth_bot_gross_usd,SUM(CASE WHEN asset='BTC' THEN bot_gross_usd ELSE 0 END) AS btc_bot_gross_usd,SUM(CASE WHEN asset='ETH' THEN bot_net_usd ELSE 0 END) AS eth_bot_net_usd,SUM(CASE WHEN asset='BTC' THEN bot_net_usd ELSE 0 END) AS btc_bot_net_usdFROM dailyGROUP BY 1ORDER BY 1