( SELECT to_address AS adr

FROM optimism.core.fact_token_transfers tr

WHERE contract_address = '0x0000000000' ) ,

transfers AS

( SELECT DAY,

address,

token_address,

sum(amount) AS amount -- Net inflow or outflow per day

FROM

( SELECT BLOCK_TIMESTAMP::date AS DAY,

to_address AS address,

tr.contract_address AS token_address,

raw_amount AS amount

FROM optimism.core.fact_token_transfers tr --INNER JOIN addresses ad ON tr."to" = ad.adr

WHERE contract_address = lower('0x00a35FD824c717879BF370E70AC6868b95870Dfb')

UNION ALL SELECT BLOCK_TIMESTAMP::date AS DAY,

from_address AS address,

tr.contract_address AS token_address, -raw_amount AS amount

FROM optimism.core.fact_token_transfers tr --INNER JOIN addresses ad ON tr."from" = ad.adr

WHERE contract_address = lower('0x00a35FD824c717879BF370E70AC6868b95870Dfb') --Token address

) t

GROUP BY 1,

2,

3 ) ,

balances_with_gap_days AS

( SELECT t.day,

address,

SUM(amount) OVER (PARTITION BY address

ORDER BY t.day) AS balance, -- balance per day with a transfer

lead(DAY, 1, current_date()) OVER (PARTITION BY address

ORDER BY t.day) AS next_day -- the day after a day with a transfer