prices as (

select

*

from (

select

hour,

symbol,

price

from blast.price.ez_hourly_token_prices

union all

select

hour,

'ETH' as symbol,

price

from blast.price.ez_hourly_token_prices

where symbol = 'WETH')

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

,

main as (

select

trader,

symbol,

sum(case when MODIFICATION_TYPE = 'deposit' then amount end) as volume_deposit,

sum(case when MODIFICATION_TYPE = 'withdraw' then amount end) as volume_withdraw,

coalesce(volume_deposit,0) + coalesce(volume_withdraw,0) as net

from blast.blitz.ez_clearing_house_events

group by 1 , 2

select

trader,

round(net_usd) as liquidity_provided,

rank_

from (