WITH Transfers as (

select

block_timestamp ,

TX_ID ,

TRANSFER_TYPE ,

SENDER ,

RECEIVER ,

AMOUNT,

DECIMAL,

currency,

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

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

Sender_Chain|| ' To ' || Receiver_Chain Transfer_Path

from axelar.core.fact_transfers

where transfer_type in ('IBC_TRANSFER_IN','IBC_TRANSFER_OUT')

and currency = 'uusdc'

and TX_SUCCEEDED = 'TRUE'

select

iff(TRANSFER_TYPE='IBC_TRANSFER_OUT',Receiver_Chain,Sender_Chain) app_OR_chain,

count (distinct tx_id) num_transfers,

count (DISTINCT SENDER) num_senders,

count (DISTINCT RECEIVER ) num_RECEIVERs,

sum ((amount/POW (10,decimal))) transferred_volume,

avg ((amount/POW (10,decimal))) weekly_avg_volume,

median ((amount/POW (10,decimal))) Median_Volume,

min ((amount/POW (10,decimal))) Minimum_Volume,

max ((amount/POW (10,decimal))) Maximum_Volume

from Transfers

group by 1 order by num_transfers desc limit 10