lower(symbol) as symbol,

avg(price) as price_usd

from osmosis.core.dim_prices

group by 1, 2

),

raw as (select regexp_substr(sender,'[a-zA-Z]+|\d+') as Sender_Chain,

regexp_substr(receiver,'[a-zA-Z]+|\d+') as Receiver_Chain,

Sender_Chain|| ' To ' || Receiver_Chain as Transfer_Path,

lower(split(currency,'-')[0]) as Symbol1,

iff(Symbol1 ilike 'u%',substring(Symbol1,2,LEN(Symbol1)), Symbol1) as asset, *

from axelar.core.fact_transfers t

)

select date_trunc('day', block_timestamp) as day,

count(distinct TX_ID) as trxs,

sum(amount/pow(10,decimal) * price_usd) as usd_volume,

initcap(asset) as symbol,

row_number() over (partition by day order by trxs DESC) as rank

from raw t join prices on date(t.block_timestamp) = pdate and prices.symbol = asset

where day>'2022-11-04'

and TX_SUCCEEDED = 'TRUE'

and TRANSFER_TYPE in ('IBC_TRANSFER_IN', 'IBC_TRANSFER_OUT')

and amount is not null

group by 1, 4

qualify rank<=10