ibc_transfers as (

select

block_timestamp,

tx_id,

try_base64_decode_string(msg:attributes[2]:value::string) as source,

try_base64_decode_string(msg:attributes[4]:value::string) as destination,

regexp_substr(try_base64_decode_string(msg:attributes[6]:value::string),'^[0-9]*')::int as amount_raw,

try_base64_decode_string(msg:attributes[7]:value::string) as denom

from axelar.core.fact_msgs

where msg_type = 'depositConfirmation'

and tx_succeeded

and try_base64_decode_string(msg:attributes[2]:key::string) = 'sourceChain'

and try_base64_decode_string(msg:attributes[4]:key::string) = 'destinationChain'

and source <> destination

and amount_raw > 0

),

aggregated as (

select

date_trunc('day',block_timestamp) as date,

count(distinct tx_id) as transfers,

avg(transfers) over (order by date rows between 6 preceding and current row) as avg_7d,

avg(transfers) over (order by date rows between 13 preceding and current row) as avg_14d,

avg(transfers) over (order by date rows between 29 preceding and current row) as avg_30d,

count(distinct denom) as currencies

from ibc_transfers

group by 1