Query editor
--Pre-iUSDTWITH raw AS (SELECTevt_block_time,"from" AS address,-value / 1e6 AS amountFROM erc20_ethereum.evt_transfer evtWHERE evt.contract_address = 0xd9b2CB2FBAD204Fc548787EF56B918c845FCce40UNION ALLSELECTevt_block_time,"to" AS address,value / 1e6 AS amountFROM erc20_ethereum.evt_transfer evtWHERE evt.contract_address = 0xd9b2CB2FBAD204Fc548787EF56B918c845FCce40),-- Balance progression over timerunning_balances AS (SELECTaddress,evt_block_time,SUM(amount) OVER (PARTITION BY addressORDER BY evt_block_timeROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS balanceFROM raw),-- First time balance becomes > 0first_positive_balance AS (SELECTaddress,MIN(evt_block_time) AS first_hold_timeFROM running_balancesWHERE balance > 0AND address <> 0x0000000000000000000000000000000000000000GROUP BY address),-- New holders per daydaily_new_holders AS (SELECTDATE_TRUNC('hour', first_hold_time) AS time,COUNT(*) AS new_holdersFROM first_positive_balanceGROUP BY 1)SELECTtime,new_holders,SUM(new_holders) OVER (ORDER BY time) AS cumulative_holdersFROM daily_new_holdersORDER BY 1 DESC;