select *,

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

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

concat(from_chain , ' --> ' , to_chain) as chain_pairs,

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

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

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)::date as date ,

chain_pairs,

count (distinct tx_id) as txs_count,

sum(txs_count)over(partition by chain_pairs order by date rows between unbounded preceding and current row ) as cumulative_txs_count,

sum(amount/10e6) as usd_vol,

max(amount/10e6) as Max_Vol,

avg(amount/10e6) as avg_vol,

median(amount/10e6) as Med_Vol,

min(amount/10e6) as Min_Vol

from basee

where BLOCK_TIMESTAMP::date >= CURRENT_DATE - {{days_back}}

group by 1,2