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('week',

block_timestamp) as date,

count(distinct TX_ID) as tx_count,

sum(amount/pow(10,decimal) *

price_usd) as tvol, initcap(asset),

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'