value:"DATE" as date,

value:"DEPOSIT_VOLUME" as deposit_volume,

value:"WITHDRAW_VOLUME" as withdraw_volume,

value:"TVL" as tvl,

value,

from (

SELECT livequery.live.udf_api(

'https://flipsidecrypto.xyz/api/v1/queries/8279cd8b-d02d-4fc9-9178-c6c53964aac5/data/latest') as resp

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

,

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