Query editor
WITH wallet_30d AS (SELECTwallet,SUM(CASE WHEN asset = 'USDC' THEN net_flow ELSE 0 END) AS usdc_net_30d,SUM(CASE WHEN asset = 'WETH' THEN net_flow ELSE 0 END) AS weth_net_30dFROM dune.dykdo0101.result_usdc_whale_wallet_flows_90dWHERE day >= CURRENT_DATE - INTERVAL '30' DAYGROUP BY 1),classified AS (SELECTwallet,usdc_net_30d,weth_net_30d,CASEWHEN usdc_net_30d > 0 AND weth_net_30d < 0 THEN 'Risk-off: rotating to USDC'WHEN usdc_net_30d < 0 AND weth_net_30d > 0 THEN 'Risk-on: deploying USDC into WETH'WHEN usdc_net_30d > 0 THEN 'Building dry powder'WHEN usdc_net_30d < 0 THEN 'Deploying stablecoin'ELSE 'Neutral'END AS signal_30dFROM wallet_30d),selected_wallets AS (SELECTwallet,SUBSTR(CAST(wallet AS VARCHAR), 1, 8) || '...' || SUBSTR(CAST(wallet AS VARCHAR), LENGTH(CAST(wallet AS VARCHAR)) - 3, 4) AS wallet_label,usdc_net_30d,ROW_NUMBER() OVER (ORDER BY ABS(usdc_net_30d) DESC) AS wallet_rankFROM classifiedWHERE signal_30d = 'Risk-off: rotating to USDC'),date_spine AS (SELECT dayFROM UNNEST(SEQUENCE(CURRENT_DATE - INTERVAL '29' DAY, CURRENT_DATE, INTERVAL '1' DAY)) AS t(day)),daily_flows AS (SELECTwallet,CAST(day AS DATE) AS day,SUM(net_flow) AS daily_usdc_flowFROM dune.dykdo0101.result_usdc_whale_wallet_flows_90dWHERE asset = 'USDC'AND day >= CURRENT_DATE - INTERVAL '30' DAYGROUP BY 1, 2),wallet_days AS (SELECT'Risk-off: rotating to USDC' AS signal_30d,sw.wallet,sw.wallet_label,sw.wallet_rank,ds.day,DATE_DIFF('day', CURRENT_DATE - INTERVAL '29' DAY, ds.day) AS day_index,COALESCE(df.daily_usdc_flow, 0) AS daily_usdc_flowFROM selected_wallets swCROSS JOIN date_spine dsLEFT JOIN daily_flows dfON df.wallet = sw.walletAND df.day = ds.day