Query editor
--------------with addresses(address,name) AS (values(0x9ebf91b8d6ff68aa05545301a3d0984eaee54a03, 'APESTR'),(0xb3d6e9e142a785ea8a4f0050fee73bcc3438c5c5, 'PUDGYSTR'),(0x6bcba7cd81a5f12c10ca1bf9b36761cc382658e8, 'BIRBSTR'),(0xc9b2c00f31b210fcea1242d91307a5b1e3b2be68, 'MEEBSTR'),(0x8680acfacb3fed5408764343fc7e8358e8c85a4c, 'DICKSTR'),(0x742fd09cBBeb1EC4E3d6404DfC959A324Deb50E6, 'SQUIGSTR'),(0x92cedfdbce6e87b595e4a529afa2905480368af4, 'TOADSTR'),(0x5d855d8a3090243fed9bf73999eedfbc2d1dcf21, 'GOBSTR'),(0xdfc3af477979912ec90b138d3e5552d5304c5663, 'PAINSTR'),(0x2090dc81f42f6ddd8deace0d3c3339017417b0dc, 'CHKSTR'),(0xd0cc2b0efb168bfe1f94a948d8df70fa10257196, 'VIBESTR')),thresh as (SELECT 0 as threshhold ),decimals as (SELECT 18 as decimals ),days AS(select cast(day as timestamp) as dayfrom unnest(sequence(date('2025-09-17'), cast(now() as date), interval '1' day)) as t(day) --),ape_transfers AS (SELECTDAY,address,token_address,sum(amount) AS amount -- Net inflow or outflow per dayFROM(SELECTdate_trunc('day', evt_block_time) AS DAY,to AS address,tr.contract_address AS token_address,cast(value as int256) AS amountFROM erc20_ethereum.evt_transfer tr, addressesWHERE contract_address = (select address from addresses where name = 'APESTR')and evt_block_time > cast('2025-09-01' as timestamp)UNION ALLSELECTdate_trunc('day', evt_block_time) AS DAY,"from" AS address,tr.contract_address AS token_address,-cast(value as int256) AS amountFROM erc20_ethereum.evt_transfer tr, addressesWHERE contract_address = (select address from addresses where name = 'APESTR')and evt_block_time > cast('2025-09-01' as timestamp)) as tGROUP BY1, 2, 3),ape_balances_with_gap_days AS(SELECTt.day,address,SUM(amount) OVER (PARTITION BY address ORDER BY t.day asc) AS balance, -- balance per day with a transferlead(DAY, 1, now() ) OVER (PARTITION BY address ORDER BY t.day asc) AS next_day -- the day after a day with a transfer