with base as ( (select date(a.BLOCK_TIMESTAMP) as date, a.TX_HASH, a.ORIGIN_FROM_ADDRESS as user, a.AMOUNT, a.AMOUNT_USD, 'Avalanche' as source, case when b.DECODED_LOG:_dstChainId = '110' then 'Arbitrum' when b.DECODED_LOG:_dstChainId = '102' then 'BSC' end as destination, source || ' ---> ' || destination as bridge_route from avalanche.core.ez_token_transfers a inner join avalanche.core.ez_decoded_event_logs b on b.tx_hash = a.tx_hash and b.BLOCK_TIMESTAMP=a.BLOCK_TIMESTAMP where a.CONTRACT_ADDRESS = '0x6e84a6216ea6dacc71ee8e6b0a5b7322eebc0fdd' and a.ORIGIN_TO_ADDRESS = '0x371c7ec6d8039ff7933a2aa28eb827ffe1f52f07' and a.ORIGIN_FUNCTION_SIGNATURE = '0x2cdf0b95' and b.event_name = 'SendToChain' and a.BLOCK_TIMESTAMP <= current_date - 1) union all (select date(a.BLOCK_TIMESTAMP) as date, a.TX_HASH, a.ORIGIN_FROM_ADDRESS as user, a.AMOUNT, a.AMOUNT_USD, 'Arbitrum' as source, case when b.DECODED_LOG:_dstChainId = '106' then 'Avalanche' when b.DECODED_LOG:_dstChainId = '102' then 'BSC' end as destination, source || ' ---> ' || destination as bridge_route from Arbitrum.core.ez_token_transfers a inner join Arbitrum.core.ez_decoded_event_logs b on b.tx_hash = a.tx_hash and b.BLOCK_TIMESTAMP=a.BLOCK_TIMESTAMP where a.CONTRACT_ADDRESS = '0x371c7ec6d8039ff7933a2aa28eb827ffe1f52f07' and a.ORIGIN_TO_ADDRESS = '0x371c7ec6d8039ff7933a2aa28eb827ffe1f52f07' and a.ORIGIN_FUNCTION_SIGNATURE = '0x2cdf0b95' and b.event_name = 'SendToChain' and a.BLOCK_TIMESTAMP <= current_date - 1) union all (select date(a.BLOCK_TIMESTAMP) as date, a.TX_HASH, a.ORIGIN_FROM_ADDRESS as user, a.AMOUNT, a.AMOUNT_USD, 'BSC' as source, case when b.DECODED_LOG:_dstChainId = '106' then 'Avalanche' when b.DECODED_LOG:_dstChainId = '110' then 'Arbitrum' end as destination, source || ' ---> ' || destination as bridge_route from bsc.core.ez_token_transfers a inner join bsc.core.ez_decoded_event_logs b on b.tx_hash = a.tx_hash and b.BLOCK_TIMESTAMP=a.BLOCK_TIMESTAMP where a.CONTRACT_ADDRESS = '0x371c7ec6d8039ff7933a2aa28eb827ffe1f52f07' and a.ORIGIN_TO_ADDRESS = '0x371c7ec6d8039ff7933a2aa28eb827ffe1f52f07' and a.ORIGIN_FUNCTION_SIGNATURE = '0x2cdf0b95' and b.event_name = 'SendToChain' and a.BLOCK_TIMESTAMP <= current_date - 1)), user_volume as ( select DISTINCT USER, SUM(AMOUNT) AS JOE_BRIDGED_VOLUME, SUM(AMOUNT_USD) AS USD_BRIDGED_VOLUME from base GROUP BY 1 ORDER BY 1) select case when USD_BRIDGED_VOLUME < 5 then 'a. Under 5 USD' when USD_BRIDGED_VOLUME >= 5 and USD_BRIDGED_VOLUME < 15 then 'b. Between 6-15 USD' when USD_BRIDGED_VOLUME >= 15 and USD_BRIDGED_VOLUME < 30 then 'c. Between 16-30 USD' when USD_BRIDGED_VOLUME >= 30 and USD_BRIDGED_VOLUME < 50 then 'd. Between 31-50 USD' when USD_BRIDGED_VOLUME >= 50 and USD_BRIDGED_VOLUME < 75 then 'e. Between 51-75 USD' when USD_BRIDGED_VOLUME >= 75 and USD_BRIDGED_VOLUME < 125 then 'f. Between 76-125 USD' when USD_BRIDGED_VOLUME >= 125 and USD_BRIDGED_VOLUME < 250 then 'g. Between 126-250 USD' when USD_BRIDGED_VOLUME >= 250 and USD_BRIDGED_VOLUME < 500 then 'h. Between 251-500 USD' when USD_BRIDGED_VOLUME >= 500 and USD_BRIDGED_VOLUME < 1000 then 'i. Between 501-1000 USD' when USD_BRIDGED_VOLUME >= 1000 then 'j. Above 1000 USD' end as volume_levels, count(distinct user) as total_users from user_volume group by 1 having volume_levels is not null order by 1

TraderJoe Bridge Analysis