select sum(raw_amount) as yield, block_timestamp as block3 from avalanche.core.fact_token_transfers

where contract_address = '0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e'

and to_address = '0x2eaf73f8e6bcf606f56e5cf201756c1f0565c068'

and

(from_address = '0x69c8367bfe0dd9b26284617353b58319241a6763'

or from_address = '0xe203647cc2b89860735ae191f13de44749cd37c8'

or from_address = '0x8f723a11c674cab9ba4c71bb5df3c519eea0fd0a')

group by block_timestamp

order by block_timestamp desc

),

inflow(deposits, block2) as (

select raw_amount as deposits, block_timestamp as block2 from avalanche.core.fact_token_transfers

where contract_address = '0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e'

and to_address = '0x2eaf73f8e6bcf606f56e5cf201756c1f0565c068'

),

outflow(withdrawals, block1) as (

select raw_amount as withdrawals, block_timestamp as block1 from avalanche.core.fact_token_transfers

where contract_address = '0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e'

and from_address = '0x2eaf73f8e6bcf606f56e5cf201756c1f0565c068'

and to_address != '0xe203647cc2b89860735ae191f13de44749cd37c8'

and to_address != '0x8f723a11c674cab9ba4c71bb5df3c519eea0fd0a'

and to_address != '0x69c8367bfe0dd9b26284617353b58319241a6763'

),

y (tvl_no_withdraws, block4) as

(select COALESCE(deposits,0)+ COALESCE(yield,0) as tvl_no_withdraws, block2 as block4

from inflow full join rewards

on inflow.block2 = rewards.block3

and rewards.yield = inflow.deposits

order by block4

),

z (Tvl, block) AS

(select sum(((tvl_no_withdraws)-COALESCE(withdrawals,0))/1000000) as tvl, block4 as block

from y full join outflow

on y.block4 = outflow.block1

group by block)