Query editor
WITH wallet_30d AS (SELECT wallet, 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 (SELECT wallet, 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 (SELECT wallet, 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 = 'Deploying stablecoin'),date_spine AS (SELECT dayFROM UNNEST(SEQUENCE(CURRENT_DATE - INTERVAL '29' DAY, CURRENT_DATE, INTERVAL '1' DAY)) AS t(day)),daily_flows AS (SELECT wallet, 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 'Deploying stablecoin' 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 df ON df.wallet = sw.wallet AND df.day = ds.dayWHERE sw.wallet_rank <= 10)SELECT signal_30d, wallet, wallet_label, wallet_rank, day, day_index, daily_usdc_flow,SUM(daily_usdc_flow) OVER (PARTITION BY wallet ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_usdc_flowFROM wallet_daysORDER BY wallet_rank, day