select

date(recorded_at) as pdate, lower(symbol) as symbol,

avg(price) as price_usd

from osmosis.core.dim_prices

group by pdate, symbol

),

raw as (

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

date_trunc('month', block_timestamp) as date, count(distinct TX_ID) as tx_count,

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

row_number() over (partition by date order by tx_count DESC) as r

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

where TX_SUCCEEDED = 'TRUE'

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

and amount is not null

group by date, Transfer_Path

qualify r<=10 order by date