with deposit as (

select

symbol,

sum(amount) as volume_deposit

from arbitrum.vertex.ez_clearing_house_events

where MODIFICATION_TYPE = 'deposit'

group by 1

,

withdraw as (

select

symbol,

sum(-1 * amount) as volume_withdraw

from arbitrum.vertex.ez_clearing_house_events

where MODIFICATION_TYPE = 'withdraw'

group by 1

,

prices as (

select

symbol,

price

from arbitrum.price.ez_prices_hourly

where symbol in ('ARB', 'USDT','VRTX', 'USDC', 'WETH','WBTC')

qualify row_number () over (partition by symbol order by hour desc) = 1

,

final as (

select

a.symbol,

volume_deposit - volume_withdraw as tvl

from deposit a

join withdraw b using(symbol)