with Transfers as (

select

block_timestamp ,

TX_ID ,

TRANSFER_TYPE ,

SENDER ,

RECEIVER ,

AMOUNT,

DECIMAL,

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 date_trunc (week,block_timestamp) weekly,

transfer_Type,

count (distinct tx_id) num_transfers,

sum (num_transfers) over (partition by transfer_type order by weekly) cum_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,

sum (transferred_volume) over (partition by transfer_type order by weekly) cum_volume

from Transfers

group by 1,2