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

'sei1hrndqntlvtmx2kepr0zsfgr7nzjptcc72cr4ppk4yav58vvy7v3s4er8ed' as token_address,

avg(value[1]) as token_price

from (

SELECT livequery.live.udf_api(

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

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

GROUP by 1,2),

tbl as (

select

BLOCK_TIMESTAMP,

SWAPPER,

TX_ID,

CURRENCY_in,

CURRENCY_out,

((AMOUNT_out/pow(10,6))* b.token_price) as buy_volume,

((AMOUNT_in/pow(10,6))* c.token_price) as sell_volume,

(AMOUNT_out/pow(10,6)) as buy_amount,

(AMOUNT_in/pow(10,6)) as sell_amount

from sei.defi.fact_dex_swaps

a left join prices b on trunc(a.BLOCK_TIMESTAMP::date,'day')=b.hour and a.CURRENCY_out= b.token_address

left JOIN prices c on trunc(a.BLOCK_TIMESTAMP::date,'day')=c.hour and a.CURRENCY_in= c.token_address

CURRENCY_in = 'sei1hrndqntlvtmx2kepr0zsfgr7nzjptcc72cr4ppk4yav58vvy7v3s4er8ed' OR

CURRENCY_out = 'sei1hrndqntlvtmx2kepr0zsfgr7nzjptcc72cr4ppk4yav58vvy7v3s4er8ed'

),

buyer as (SELECT

SWAPPER,

sum(ifnull(buy_volume,0)) as total_buy,

sum(ifnull(buy_amount,0)) as total_buy_amount,

from tbl