select

date_trunc('day', block_timestamp) as date,

symbol,

sum(amount) as volume,

sum(volume) over (partition by symbol order by date) as cum_deposit

from blast.blitz.ez_clearing_house_events

where MODIFICATION_TYPE = 'deposit'

group by 1 , 2

,

withdraw as (

select

date_trunc('day', block_timestamp) as date,

symbol,

sum(-1 * amount) as volume,

sum(volume) over (partition by symbol order by date) as cum_withdraw

from blast.blitz.ez_clearing_house_events

where MODIFICATION_TYPE = 'withdraw'

group by 1 , 2

, final as (

select

a.date,

a.symbol,

cum_deposit - cum_withdraw as tvl

from deposit a

left join withdraw b using(date,symbol)

, prices as (

select

date_trunc('day',hour ) as date,

symbol,

avg(price) as price_usd

from blast.price.ez_hourly_token_prices