Query editor
-- Story: 고래는 알고 있었다-- Reusable cohort: current top 200 Ethereum USDC holdersWITH inflows AS (SELECT"to" AS wallet,SUM(CAST(value AS DOUBLE) / POWER(10, 6)) AS amountFROM erc20_ethereum.evt_TransferWHERE contract_address = 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48AND "to" != 0x0000000000000000000000000000000000000000GROUP BY 1),outflows AS (SELECT"from" AS wallet,-SUM(CAST(value AS DOUBLE) / POWER(10, 6)) AS amountFROM erc20_ethereum.evt_TransferWHERE contract_address = 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48AND "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