with DestinationChainT as ( select block_timestamp, tx_id, attribute_value as Destination_Chain from axelar.core.fact_msg_attributes where tx_succeeded = 'TRUE' and attribute_key = 'destinationChain'), DestinatinoUserT as ( select block_timestamp, tx_id, attribute_value as Destination_User from axelar.core.fact_msg_attributes where tx_succeeded = 'TRUE' and attribute_key = 'destinationAddress' and tx_id in (select tx_id from DestinationChainT)), OriginChainT as ( select block_timestamp, tx_id, attribute_value as Origin_Chain from axelar.core.fact_msg_attributes where tx_succeeded = 'TRUE' and attribute_key = 'sourceChain' and tx_id in (select tx_id from DestinationChainT)), OriginUserT as ( select block_timestamp, tx_id, attribute_value as Origin_User from axelar.core.fact_msg_attributes where tx_succeeded = 'TRUE' and attribute_key = 'sender' and tx_id in (select tx_id from DestinationChainT)), BridgeT as ( select block_timestamp, tx_id, attribute_value from axelar.core.fact_msg_attributes where msg_type = 'coin_spent' and attribute_index = 1 and tx_id in (select tx_id from DestinationChainT) and tx_succeeded = 'TRUE'), maintable1 as ( select t1.block_timestamp, t1.tx_id, Origin_Chain, Destination_Chain, Origin_User, Destination_User, Origin_Chain || ' To ' || Destination_Chain as Transfer_Path, attribute_value as tokenn, --Credit This Part: JackTheGuy case when attribute_value like '%uaxl%' then 'AXL' when attribute_value like '%uusdc%' then 'USDC' when attribute_value like '%ibc/6F4968A73F90CF7DE6394BF937D6DF7C7D162D74D839C13F53B41157D315E05F%' then 'UST' when attribute_value like '%ibc/9117A26BA81E29FA4F78F57DC2BD90CD3D26848101BA880445F119B22A1E254E%' then 'ATOM' when attribute_value like '%weth-wei%' then 'ETH' when attribute_value like '%ibc/4627AD2524E3E0523047E35BB76CC90E37D9D57ACF14F0FCBCEB2480705F3CB8%' then 'LUNA' when attribute_value like '%wbtc-satoshi%' then 'WBTC' else 'Other' end as Token_Name from OriginChainT t1 join OriginUserT t2 on t1.tx_id = t2.tx_id and t1.block_timestamp::date = t2.block_timestamp::date join DestinationChainT t3 on t1.tx_id = t3.tx_id and t1.block_timestamp::date = t3.block_timestamp::date join DestinatinoUserT t4 on t1.tx_id = t4.tx_id and t1.block_timestamp::date = t4.block_timestamp::date join BridgeT t5 on t1.tx_id = t5.tx_id and t1.block_timestamp::date = t5.block_timestamp::date), maintable2 as ( select block_timestamp, tx_id, Origin_Chain, Destination_Chain, Origin_User, Destination_User, Token_name, Transfer_Path, tokenn, --Credit This Part: JackTheGuy case when tokenn like '%uaxl%' then cast(REPLACE(tokenn, 'uaxl', '') as INT) / power(10, 6) when tokenn like '%uusdc%' then cast(REPLACE(tokenn, 'uusdc', '') as INT) / power(10, 6) when tokenn like '%ibc/6F4968A73F90CF7DE6394BF937D6DF7C7D162D74D839C13F53B41157D315E05F%' then cast(REPLACE(tokenn, 'ibc/6F4968A73F90CF7DE6394BF937D6DF7C7D162D74D839C13F53B41157D315E05F', '') as INT) / power(10, 6) when tokenn like '%ibc/9117A26BA81E29FA4F78F57DC2BD90CD3D26848101BA880445F119B22A1E254E%' then cast(REPLACE(tokenn, 'ibc/9117A26BA81E29FA4F78F57DC2BD90CD3D26848101BA880445F119B22A1E254E', '') as INT) / power(10, 6) when tokenn like '%weth-wei%' then cast(REPLACE(tokenn, 'weth-wei', '') as INT) / power(10, 18) when tokenn like '%ibc/4627AD2524E3E0523047E35BB76CC90E37D9D57ACF14F0FCBCEB2480705F3CB8%' then cast(REPLACE(tokenn, 'ibc/4627AD2524E3E0523047E35BB76CC90E37D9D57ACF14F0FCBCEB2480705F3CB8', '') as INT) / power(10, 6) when tokenn like '%wbtc-satoshi%' then cast(REPLACE(tokenn, 'wbtc-satoshi', '') as INT) / power(10, 9) end as amount from maintable1), pricet as ( select recorded_hour::date as day, symbol, median (open) as USDPrice from crosschain.core.fact_hourly_prices left outer join crosschain.core.dim_asset_metadata on crosschain.core.fact_hourly_prices.id like crosschain.core.dim_asset_metadata.id where symbol in (select token_name from maintable2) group by 1,2), Finaltable as ( select t1.block_timestamp block_timestamp, tx_id, Origin_Chain, Destination_Chain, Origin_User, Destination_User, Token_name, tokenn, Transfer_Path, amount, amount*USDPrice as USD_Volume from maintable2 t1 full outer join pricet t2 on t1.block_timestamp::Date = t2.day and t1.token_name = t2.symbol), osmopricet as ( select recorded_at::date as day, avg (price) as USDPrice from osmosis.core.dim_prices where symbol = 'OSMO' group by 1), table1 as ( select Destination_User, count (distinct tx_id) as TX_Count, count (distinct origin_user) as Origin_Users_Count, count (distinct destination_user) as Destination_Users_Count, sum (USD_Volume) as Total_USD_Volume from Finaltable group by 1), balancet as ( select user_address, sum (usd_value_now) as balance from ethereum.core.ez_current_balances where user_address in (select destination_user from table1) and usd_value_now < 1e9 group by 1 union all select address, sum (balance*usdprice/pow(10,decimal)) as balance from osmosis.core.fact_daily_balances t1 join osmopricet t2 on t1.date = t2.day where currency = 'uosmo' and date = (select max(date) from osmosis.core.fact_daily_balances) and address in (select destination_user from table1) group by 1), daytable as ( select date_trunc(day,block_timestamp) as date, Destination_User, case when balance < 100 then 'Less Than $100' when balance >= 100 and balance < 1000 then '$100 - $1000 Balance' when balance >= 1000 and balance < 10000 then '$1000 - $10000 Balance' when balance >= 10000 and balance < 100000 then '$10000 - $100000 Balance' else 'More Than $100000 Balance' end as balance_type, count (distinct tx_id) as TX_Count, count (distinct origin_user) as Origin_Users_Count, count (distinct destination_user) as Destination_Users_Count, sum (USD_Volume) as Total_USD_Volume from Finaltable t1 join balancet t2 on t1.destination_user = t2.user_address group by 1,2,3), weektable as ( select date_trunc(week,block_timestamp) as date, Destination_User, case when balance < 100 then 'Less Than $100' when balance >= 100 and balance < 1000 then '$100 - $1000 Balance' when balance >= 1000 and balance < 10000 then '$1000 - $10000 Balance' when balance >= 10000 and balance < 100000 then '$10000 - $100000 Balance' else 'More Than $100000 Balance' end as balance_type, count (distinct tx_id) as TX_Count, count (distinct origin_user) as Origin_Users_Count, count (distinct destination_user) as Destination_Users_Count, sum (USD_Volume) as Total_USD_Volume from Finaltable t1 join balancet t2 on t1.destination_user = t2.user_address group by 1,2,3), monthtable as ( select date_trunc(month,block_timestamp) as date, Destination_User, case when balance < 100 then 'Less Than $100' when balance >= 100 and balance < 1000 then '$100 - $1000 Balance' when balance >= 1000 and balance < 10000 then '$1000 - $10000 Balance' when balance >= 10000 and balance < 100000 then '$10000 - $100000 Balance' else 'More Than $100000 Balance' end as balance_type, count (distinct tx_id) as TX_Count, count (distinct origin_user) as Origin_Users_Count, count (distinct destination_user) as Destination_Users_Count, sum (USD_Volume) as Total_USD_Volume from Finaltable t1 join balancet t2 on t1.destination_user = t2.user_address group by 1,2,3) select 'Daily' as timespan, balance_type, avg (tx_count) as Average_Bridges, median (tx_count) as Median_Bridges, min (tx_count) as Minimum_Bridges, max (tx_count) as Maximum_Bridges, avg (origin_users_count) as Average_Origin_Users, avg (Destination_Users_Count) as Average_Destination_users, avg (Total_usd_volume) as Average_USD_Volume, median (total_usd_volume) as Median_USD_Volume from daytable group by 1,2 union ALL select 'Weekly' as timespan, balance_type, avg (tx_count) as Average_Bridges, median (tx_count) as Median_Bridges, min (tx_count) as Minimum_Bridges, max (tx_count) as Maximum_Bridges, avg (origin_users_count) as Average_Origin_Users, avg (Destination_Users_Count) as Average_Destination_users, avg (Total_usd_volume) as Average_USD_Volume, median (total_usd_volume) as Median_USD_Volume from Weektable group by 1,2 union ALL select 'Monthly' as timespan, balance_type, avg (tx_count) as Average_Bridges, median (tx_count) as Median_Bridges, min (tx_count) as Minimum_Bridges, max (tx_count) as Maximum_Bridges, avg (origin_users_count) as Average_Origin_Users, avg (Destination_Users_Count) as Average_Destination_users, avg (Total_usd_volume) as Average_USD_Volume, median (total_usd_volume) as Median_USD_Volume from Monthtable group by 1,2

Axelar Bridge