with maintable as (

select *,

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 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 (month,block_timestamp) as date,

transfer_Type,

count (distinct tx_id) as Transfers_Count,

sum ((amount/POW (10,decimal))) as Total_Volume,

avg ((amount/POW (10,decimal))) as Average_Volume,

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

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

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

sum (Total_Volume) over (partition by transfer_type order by date) as Cumulative_Volume,

sum (Transfers_Count) over (partition by transfer_type order by date) as Cumulative_Transfers,

avg (Total_Volume) over (partition by transfer_type order by date rows between 60 PRECEDING and current row) as Moving_Average_60,

avg (Total_Volume) over (partition by transfer_type order by date rows between 7 PRECEDING and current row) as Moving_Average_7

from maintable

group by 1,2

order by 1