Query editor
WITH base AS (SELECTblock_date AS day,taker,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.tradesWHERE blockchain = 'ethereum'AND block_month >= date_trunc('month', CURRENT_DATE - INTERVAL '120' DAY)AND block_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'))),trader_day AS (SELECTday,taker,asset,SUM(amount_usd) AS gross_usd,SUM(signed_usd) AS net_usdFROM baseWHERE asset IS NOT NULLGROUP BY 1,2,3),whale_day AS (SELECT *FROM trader_dayWHERE gross_usd >= 1000000),daily AS (SELECTday,asset,COUNT(DISTINCT taker) AS whale_count,SUM(gross_usd) AS whale_gross_usd,SUM(net_usd) AS whale_net_usdFROM whale_dayGROUP BY 1,2)SELECTday,SUM(CASE WHEN asset = 'ETH' THEN whale_count ELSE 0 END) AS eth_whale_count,SUM(CASE WHEN asset = 'BTC' THEN whale_count ELSE 0 END) AS btc_whale_count,SUM(CASE WHEN asset = 'ETH' THEN whale_gross_usd ELSE 0 END) AS eth_whale_gross_usd,SUM(CASE WHEN asset = 'BTC' THEN whale_gross_usd ELSE 0 END) AS btc_whale_gross_usd,SUM(CASE WHEN asset = 'ETH' THEN whale_net_usd ELSE 0 END) AS eth_whale_net_usd,SUM(CASE WHEN asset = 'BTC' THEN whale_net_usd ELSE 0 END) AS btc_whale_net_usdFROM dailyGROUP BY 1