select

hour::date as p_date,

symbol,

avg(price) as usd_price

from crosschain.price.ez_prices_hourly

where blockchain = 'ethereum'

group by 1 ,2

select *

select

distinct user,

sum(amount_usd) as "Total USD Volume",

count(distinct tx) as "Total TXs",

rank() over(order by "Total USD Volume" desc) as "Volume Rank",

rank() over(order by "Total TXs" desc) as "TX Rank"

distinct receiver as user,

tx_hash as tx,

(amount_unadj/pow(10, decimals))*usd_price as amount_usd

from aptos.defi.fact_bridge_activity a

join aptos.core.dim_tokens b using(token_address)

join pricet c on block_timestamp::date = p_date::date and lower(b.symbol) = lower(c.symbol)

where platform = 'layerzero'

and receiver != sender

distinct sender as user,

tx_hash as tx,

(amount_unadj/pow(10, decimals))*usd_price as amount_usd