from blast.blitz.ez_clearing_house_events

where MODIFICATION_TYPE = 'deposit'

group by 1,2)

,

withdraw as ( select symbol, trader, sum(amount) as withdraw_amount

from blast.blitz.ez_clearing_house_events

where MODIFICATION_TYPE = 'withdraw'

group by 1,2)

,

price as ( Select trunc(TO_TIMESTAMP(value[0]::string),'hour') as hour, value[1] as avg_price

from (

SELECT livequery.live.udf_api(

'https://api.coingecko.com/api/v3/coins/ethereum/market_chart?vs_currency=usd&days=90') as resp

,LATERAL FLATTEN (input => resp:data:prices)

order by 1 desc

limit 1)

,

final_amount as ( select a.symbol, a.trader, ifnull(deposit_amount,0) as dep_amount, ifnull(withdraw_amount,0) as with_amount,

dep_amount+with_amount as current_balance,

case when a.symbol = 'USDB' then current_balance else current_balance*avg_price end as volume_usd,

case when a.symbol = 'USDB' then dep_amount else dep_amount*avg_price end as dep_volume_usd,

case when a.symbol = 'USDB' then with_amount else with_amount*avg_price end as with_volume_usd

from deposit a left outer join withdraw b on a.trader = b.trader and a.symbol = b.symbol , price)

,

final_usd as ( select trader, count(DISTINCT symbol) as tokens, sum(volume_usd) as current_bal, sum(dep_volume_usd) as deposit_volume,

sum(with_volume_usd) as withdraw_volume_usd

from final_amount

group by 1)

,

deposits as ( select trader,

count(DISTINCT tx_hash) as deposit_tx,

avg(amount_usd) as avg_dep_amount,

max(amount_usd) as max_dep_amount

from blast.blitz.ez_clearing_house_events