with bridge_info 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, 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, 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 ( 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 axelar.core.fact_msg_attributes where tx_succeeded = 'TRUE' and attribute_key = 'destinationChain') )as t1 join ( 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 axelar.core.fact_msg_attributes where tx_succeeded = 'TRUE' and attribute_key = 'destinationChain') ) as t2 on t1.tx_id = t2.tx_id and t1.block_timestamp::date = t2.block_timestamp::date join ( select block_timestamp, tx_id, attribute_value as Destination_Chain from axelar.core.fact_msg_attributes where tx_succeeded = 'TRUE' and attribute_key = 'destinationChain' ) as t3 on t1.tx_id = t3.tx_id and t1.block_timestamp::date = t3.block_timestamp::date join ( 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 axelar.core.fact_msg_attributes where tx_succeeded = 'TRUE' and attribute_key = 'destinationChain') ) as t4 on t1.tx_id = t4.tx_id and t1.block_timestamp::date = t4.block_timestamp::date join ( 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 axelar.core.fact_msg_attributes where tx_succeeded = 'TRUE' and attribute_key = 'destinationChain') and tx_succeeded = 'TRUE' )as t5 on t1.tx_id = t5.tx_id and t1.block_timestamp::date = t5.block_timestamp::date ), token_price 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 bridge_info) group by 1,2 ) , bridge_all_info 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 bridge_info t1 full outer join token_price t2 on t1.block_timestamp::Date = t2.day and t1.token_name = t2.symbol ) , user_bridge_info 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 bridge_all_info group by 1 ), osmopricet as ( select recorded_at::date as day, avg (price) as USDPrice from osmosis.core.dim_prices where symbol = 'OSMO' group by 1), user_balance as ( select user_address, sum (usd_value_now) as balance from ethereum.core.ez_current_balances where user_address in (select destination_user from user_bridge_info) 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 user_bridge_info) group by 1 ) select case when day_count=1 then 'g1: 1 day' when day_count>=2 and day_count<7 then 'g2: 2-6 day' when day_count>=7 and day_count<14 then 'g3: 7-13 day' when day_count>=14 and day_count<30 then 'g4: 14-29 day' when day_count>=30 and day_count<60 then 'g5: 30-59 day' when day_count>=60 then 'g6: more than 60 day' end as type, case when balance < 100 then 'c1: Less Than $100' when balance >= 100 and balance < 1000 then '$c2: 100 - $1K' when balance >= 1000 and balance < 10000 then 'c3: $1K - $10K' when balance >= 10000 and balance < 100000 then 'c4: $10K - $100K' when balance >= 100000 and balance < 1000000 then 'c5: $100K - $1M' when balance >= 1000000 and balance < 10000000 then 'c6: $1M - $10M' else 'c7: More Than $10M' end as balance_type, count (distinct Destination_User) as Users_Count from ( select Destination_User, count (distinct tx_id) as TX_Count, count(distinct block_timestamp::date) as day_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 bridge_all_info group by 1 ) as t1 join user_balance t2 on t1.destination_user = t2.user_address group by 1,2 order by 1

Axelar Bridge