with price as ( select date(hour) as date,

avg(price) as avg_price

from ethereum.price.ez_hourly_token_prices

where symbol = 'WETH'

group by 1)

,

volume as ( select date(block_timestamp) as date,

trader,

symbol,

tx_hash,

modification_type,

case when symbol = 'USDB' then amount else amount*avg_price end as volume

from blast.blitz.ez_clearing_house_events a left outer join price b on a.block_timestamp::date = b.date)

,

price_2 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)

group by all

order by 1 desc

limit 1)

,

deposit as ( select trunc(block_timestamp,'day') as date,

tx_hash,

trader,

symbol,

amount,

case when symbol = 'USDB' then amount else amount*avg_price end as amount_usd

from blast.blitz.ez_clearing_house_events, price_2

where MODIFICATION_TYPE = 'deposit')