Q1 as (select

(amount/pow(10,decimal)) as amount_usd,

t.* from (

select

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

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

concat(sender_chain,'-->',receiver_chain) as transfer_path,

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

iff(symbol ilike 'u%', substring(symbol, 2, LEN(symbol)), symbol) as sym,

t.*

from axelar.core.fact_transfers t

where transfer_type = 'IBC_TRANSFER_IN'

and block_timestamp >= '2022-01-01'

and currency = 'uusdc'

) t)

Date(date_trunc('month',block_timestamp)) as date,

Symbol,

receiver_chain as chain,

count(distinct tx_id) as txn_volume,

sum(amount_usd) as usd_amount,

avg(amount_usd) as avg_usd_amount,

sum(txn_volume) over (partition by Symbol order by date asc rows between unbounded preceding and current row) as cum_volume_sym_txn,

sum(txn_volume) over (partition by chain order by date asc rows between unbounded preceding and current row) as cum_volume_chain_txn,

sum(usd_amount) over (partition by chain order by date asc rows between unbounded preceding and current row) as cum_amount_chain_usd,

sum(usd_amount) over (partition by Symbol order by date asc rows between unbounded preceding and current row) as cum_amount_sym_usd,

avg(avg_usd_amount) over (partition by chain order by date asc rows between unbounded preceding and current row) as running_avg_usd_amount_chain,

avg(avg_usd_amount) over (partition by Symbol order by date asc rows between unbounded preceding and current row) as running_avg_usd_amount_sym

from Q1

group by Date,Symbol,chain