Query editor
WITH whale_transfers AS (SELECTt.blockchain,CAST(t.block_time AS DATE) AS transfer_date,t.tx_hash,t.tx_from AS from_wallet,t."to" AS to_wallet,t.symbol AS token_symbol,t.contract_address AS token_address,t.amount,t.amount_usd,RANK() OVER (PARTITION BY t.blockchain ORDER BY t.amount_usd DESC) AS whale_rankFROM tokens.transfers tWHEREt.block_time >= NOW() - INTERVAL '30' DAYAND t.amount_usd > 500000AND LOWER(t.symbol) NOT IN ('usdc', 'usdt', 'dai', 'tusd', 'usdp', 'frax', 'lusd', 'gusd', 'usd+', 'usdy', 'usde', 'usd1', 'pyusd', 'fdusd', 'susds', 'susde', 'usds','eth', 'weth', 'wsteth', 'cbeth', 'wbeth', 'weeth', 'lseth', 'ethfi', 'aethusdc', 'steth','btc', 'cbbtc', 'btcb', 'lbtc', 'wbtc', 'tbtc','okb', 'bnb', 'avax','fet')),wallet_activity_last_7d AS (SELECTtx_from,COUNT(*) AS recent_tx_count,SUM(amount_usd) AS recent_usdFROM tokens.transfersWHERE block_time >= NOW() - INTERVAL '7' DAYGROUP BY tx_from),wallet_activity_prior_30d AS (SELECTtx_from,COUNT(*) AS past_tx_count,SUM(amount_usd) AS past_usdFROM tokens.transfersWHERE block_time BETWEEN NOW() - INTERVAL '30' DAY AND NOW() - INTERVAL '7' DAYGROUP BY tx_from),wallet_spike_flagged AS (SELECTCOALESCE(r.tx_from, p.tx_from) AS wallet,COALESCE(r.recent_tx_count, 0) AS recent_tx,COALESCE(p.past_tx_count, 0) AS past_tx,COALESCE(r.recent_usd, 0) AS recent_usd,COALESCE(p.past_usd, 0) AS past_usd,CASEWHEN COALESCE(r.recent_usd, 0) > COALESCE(p.past_usd, 0) * 3 AND COALESCE(r.recent_usd, 0) > 100000 THEN '⚠ Sudden Spike'ELSE NULLEND AS activity_flagFROM wallet_activity_last_7d rFULL OUTER JOIN wallet_activity_prior_30d p ON r.tx_from = p.tx_from),