prices as (

SELECT

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

'APT' as symbol,

value[1] as price

FROM (

SELECT livequery.live.udf_api(

'https://api.coingecko.com/api/v3/coins/aptos/market_chart?vs_currency=usd&days=max&interval=hourly&precision=3') as resp

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

SELECT

hour,

symbol,

avg(price) as price

FROM crosschain.price.ez_prices_hourly

WHERE BLOCKCHAIN = 'ethereum'

GROUP BY 1 ,2

),

info as (

select

distinct tx_hash, platform, direction,

case when direction='inbound' then receiver else sender end as user,

z.symbol,

price,

decimals,

avg(amount_unadj) as amount_unadj,

min(block_timestamp) as block_timestamp

from aptos.defi.fact_bridge_activity x

JOIN aptos.core.dim_tokens z on x.token_address=z.token_address

join prices y on lower(z.symbol)=lower(y.symbol) and date_trunc('hour',block_timestamp)=hour

group by 1,2,3,4,5,6,7