attribute_value as chain_destination,

from axelar.core.fact_msg_attributes

where attribute_key ilike 'destinationChain'

group by 1,2

order by 3 desc),

base2 as (select

date_trunc('day', block_timestamp) as day,

chain_destination,

msg_type,

attribute_value,

count(distinct(a.tx_id)) as bridging_count,

cast(replace(attribute_value, 'uusdc', '') as integer) / 1e6 as axlUSDC_bridged

from axelar.core.fact_msg_attributes a

left join base b

on a.tx_id = b.tx_id

where chain_destination is not null

and msg_type ilike 'coin_spent'

and attribute_value ilike '%uusdc%'

and attribute_index = 1

group by 1,2,3,4

order by 1 asc)

select

day,

initcap(chain_destination) as chain_destinations,

sum(axlUSDC_bridged) as axlUSDC_bridgeds,

sum(axlUSDC_bridgeds) over (partition by chain_destinations order by day) as cumulative_bridging_volume_by_chain,

sum(bridging_count) as bridging_counts,

sum(bridging_counts) over (partition by chain_destinations order by day) as cumulative_bridging_count_by_chain,

axlUSDC_bridgeds/bridging_counts as avg_axlUSDC_bridging_amount

from base2

group by 1,2

order by cumulative_bridging_volume_by_chain desc