Query editor
WITH top_wallets AS (SELECT walletFROM dune.dykdo0101.result_usdc_top_200_whales),daily_whale_flows AS (SELECTDATE_TRUNC('day', t.evt_block_time) AS day,SUM(CAST(t.value AS DOUBLE) / POWER(10, 6)) AS amountFROM erc20_ethereum.evt_Transfer tINNER JOIN top_wallets w ON t."from" = w.walletWHERE t.contract_address = 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48AND t.evt_block_date >= CURRENT_DATE - INTERVAL '180' DAYGROUP BY 1UNION ALLSELECTDATE_TRUNC('day', t.evt_block_time) AS day,-SUM(CAST(t.value AS DOUBLE) / POWER(10, 6)) AS amountFROM erc20_ethereum.evt_Transfer tINNER JOIN top_wallets w ON t."to" = w.walletWHERE t.contract_address = 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48AND t.evt_block_date >= CURRENT_DATE - INTERVAL '180' DAYGROUP BY 1),whale_outflow_days AS (SELECTday,SUM(amount) AS net_outflowFROM daily_whale_flowsGROUP BY 1HAVING SUM(amount) > 0ORDER BY net_outflow DESCLIMIT 10),outflow_transfers AS (SELECTt.evt_tx_hash,t.evt_block_time,t."from" AS whale_wallet,t."to" AS destination_address,CAST(t.value AS DOUBLE) / POWER(10, 6) AS usdc_amountFROM erc20_ethereum.evt_Transfer tINNER JOIN top_wallets w ON t."from" = w.walletINNER JOIN whale_outflow_days odON t.evt_block_time >= od.dayAND t.evt_block_time < od.day + INTERVAL '48' HOURWHERE t.contract_address = 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48AND t.evt_block_date >= CURRENT_DATE - INTERVAL '180' DAYAND t."to" NOT IN (SELECT wallet FROM top_wallets)),destination_labels AS (SELECTot.*,COALESCE(oa.account_owner, oa.custody_owner, od.name, c.name, e.name, 'Unlabeled') AS destination_label,COALESCE(od.primary_category, IF(c.address IS NOT NULL, 'Contract', NULL), 'Unlabeled') AS destination_category,IF(c.address IS NOT NULL, TRUE, FALSE) AS is_contractFROM outflow_transfers otLEFT JOIN labels.owner_addresses oaON oa.blockchain = 'ethereum'