Query editor
-- Story: 고래는 알고 있었다-- Dashboard position: Q4C — USDC whale outflow destination category mix-- Description: Aggregates materialized Q4 outflow transfers into interpretable venue buckets.-- Dependency: dune.dykdo0101.result_usdc_whale_outflow_transfersWITH 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 dune.dykdo0101.result_usdc_whale_outflow_transfers otLEFT JOIN labels.owner_addresses oaON oa.blockchain = 'ethereum'AND oa.address = ot.destination_addressLEFT JOIN labels.owner_details odON od.owner_key = oa.owner_keyLEFT JOIN ethereum.contracts cON c.address = ot.destination_addressLEFT JOIN labels.ens eON e.blockchain = 'ethereum'AND e.address = ot.destination_address),address_rollup AS (SELECTdestination_address,destination_label,destination_category,is_contract,SUM(usdc_amount) AS usdc_outflow,COUNT(DISTINCT evt_tx_hash) AS transfer_count,COUNT(DISTINCT whale_wallet) AS whale_wallet_countFROM destination_labelsWHERE usdc_amount > 0GROUP BY 1, 2, 3, 4),bucketed AS (SELECT*,CASEWHEN REGEXP_LIKE(LOWER(destination_label), 'coinbase|binance|kraken|okx|bybit|bitfinex|bitstamp|crypto\\.com|gemini|huobi|kucoin|gate\\.io')OR REGEXP_LIKE(LOWER(destination_category), 'cex|centralized|exchange|custody')THEN 'CEX / custody'WHEN REGEXP_LIKE(LOWER(destination_label), 'aave|compound|maple|morpho|spark|maker|sky|euler|fluid|venus')OR REGEXP_LIKE(LOWER(destination_category), 'lending|credit|loan|borrow')THEN 'Lending / credit'WHEN REGEXP_LIKE(LOWER(destination_label), 'uniswap|sushiswap|curve|balancer|1inch|paraswap|cow|kyber|pancake|matcha')OR REGEXP_LIKE(LOWER(destination_category), 'dex|amm|defi')THEN 'DEX / router'WHEN is_contract THEN 'Other contract'ELSE 'Unlabeled EOA / transfer'END AS destination_bucketFROM address_rollup)SELECTdestination_bucket,SUM(usdc_outflow) AS usdc_outflow,ROUND(SUM(usdc_outflow) / SUM(SUM(usdc_outflow)) OVER () * 100, 2) AS pct_of_total,COUNT(*) AS destination_address_count,SUM(transfer_count) AS transfer_count,