Query editor
-- Story: 고래는 알고 있었다-- Dashboard position: Q1 — USDC 고래 식별-- Description: Transfer 이벤트로 현재 잔고를 재구성해 상위 200 USDC 보유 지갑 반환-- Parameter: {{token_address}} e.g. 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48 (USDC)-- Parameter: {{token_decimals}} e.g. 6WITH inflows AS (SELECT"to" AS wallet,SUM(CAST(value AS DOUBLE) / POWER(10, {{token_decimals}})) AS amountFROM erc20_ethereum.evt_TransferWHERE contract_address = {{token_address}}AND "to" != 0x0000000000000000000000000000000000000000GROUP BY 1),outflows AS (SELECT"from" AS wallet,-SUM(CAST(value AS DOUBLE) / POWER(10, {{token_decimals}})) AS amountFROM erc20_ethereum.evt_TransferWHERE contract_address = {{token_address}}AND "from" != 0x0000000000000000000000000000000000000000GROUP BY 1),balances AS (SELECT wallet, SUM(amount) AS balanceFROM (SELECT * FROM inflows UNION ALL SELECT * FROM outflows)GROUP BY walletHAVING SUM(amount) > 0)SELECTwallet,balance,CASEWHEN balance >= 100000000 THEN 'Mega whale'WHEN balance >= 10000000 THEN 'Whale'WHEN balance >= 1000000 THEN 'Large holder'ELSE 'Holder'END AS holder_tier,ROUND(balance / SUM(balance) OVER () * 100, 4) AS pct_supply,ROUND(SUM(balance) OVER (ORDER BY balance DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)/ SUM(balance) OVER () * 100, 2) AS cumulative_pct,ROW_NUMBER() OVER (ORDER BY balance DESC) AS rankFROM balancesORDER BY balance DESCLIMIT 200