with raw_list as ( select REGEXP_SUBSTR(address_name, '\\(([^)]+)\\)', 1, 1, 'e') as asset, address, blockchain from crosschain.core.address_labels where project_name = 'stargate finance' and label_subtype = 'bridge' and asset is not null),ether_outflow as ( select symbol, count(distinct origin_from_address) as number_of_users, count(distinct tx_hash) as number_of_tx, sum(amount_usd) as outflow_amount_usd from ethereum.core.ez_token_transfers where lower(symbol) in (select asset from raw_list where blockchain = 'ethereum') and from_address in (select address from raw_list where blockchain = 'ethereum') and block_timestamp::date >= '{{start_date}}' and block_timestamp::date <= '{{end_date}}' group by 1),ether_inflow as ( select symbol, count(distinct origin_from_address) as number_of_users, count(distinct tx_hash) as number_of_tx, sum(amount_usd) as inflow_amount_usd from ethereum.core.ez_token_transfers where lower(symbol) in (select asset from raw_list where blockchain = 'ethereum') and to_address in (select address from raw_list where blockchain = 'ethereum') and block_timestamp::date >= '{{start_date}}' and block_timestamp::date <= '{{end_date}}' group by 1) ,bsc_outflow as ( select symbol, count(distinct origin_from_address) as number_of_users, count(distinct tx_hash) as number_of_tx, sum(amount_usd) as outflow_amount_usd from bsc.core.ez_token_transfers where lower(symbol) in (select asset from raw_list where blockchain = 'bsc') and from_address in (select address from raw_list where blockchain = 'bsc') and block_timestamp::date >= '{{start_date}}' and block_timestamp::date <= '{{end_date}}' group by 1),bsc_inflow as ( select symbol, count(distinct origin_from_address) as number_of_users, count(distinct tx_hash) as number_of_tx, sum(amount_usd) as inflow_amount_usd from bsc.core.ez_token_transfers where lower(symbol) in (select asset from raw_list where blockchain = 'bsc') and to_address in (select address from raw_list where blockchain = 'bsc') and block_timestamp::date >= '{{start_date}}' and block_timestamp::date <= '{{end_date}}' group by 1) ,avalanche_outflow as ( select case when symbol = 'USDt' then 'USDT' else symbol end as symbol, count(distinct origin_from_address) as number_of_users, count(distinct tx_hash) as number_of_tx, sum(amount_usd) as outflow_amount_usd from avalanche.core.ez_token_transfers where lower(symbol) in (select asset from raw_list where blockchain = 'avalanche') and from_address in (select address from raw_list where blockchain = 'avalanche') and block_timestamp::date >= '{{start_date}}' and block_timestamp::date <= '{{end_date}}' group by 1),avalanche_inflow as ( select case when symbol = 'USDt' then 'USDT' else symbol end as symbol, count(distinct origin_from_address) as number_of_users, count(distinct tx_hash) as number_of_tx, sum(amount_usd) as inflow_amount_usd from avalanche.core.ez_token_transfers where lower(symbol) in (select asset from raw_list where blockchain = 'avalanche') and to_address in (select address from raw_list where blockchain = 'avalanche') and block_timestamp::date >= '{{start_date}}' and block_timestamp::date <= '{{end_date}}' group by 1) ,avalanche_outflow as ( select symbol, count(distinct origin_from_address) as number_of_users, count(distinct tx_hash) as number_of_tx, sum(amount_usd) as outflow_amount_usd from avalanche.core.ez_token_transfers where lower(symbol) in (select asset from raw_list where blockchain = 'avalanche') and from_address in (select address from raw_list where blockchain = 'avalanche') and block_timestamp::date >= '{{start_date}}' and block_timestamp::date <= '{{end_date}}' group by 1),avalanche_inflow as ( select symbol, count(distinct origin_from_address) as number_of_users, count(distinct tx_hash) as number_of_tx, sum(amount_usd) as inflow_amount_usd from avalanche.core.ez_token_transfers where lower(symbol) in (select asset from raw_list where blockchain = 'avalanche') and to_address in (select address from raw_list where blockchain = 'avalanche') and block_timestamp::date >= '{{start_date}}' and block_timestamp::date <= '{{end_date}}' group by 1) ,arbitrum_outflow as ( select symbol, count(distinct origin_from_address) as number_of_users, count(distinct tx_hash) as number_of_tx, sum(amount_usd) as outflow_amount_usd from arbitrum.core.ez_token_transfers where lower(symbol) in (select asset from raw_list where blockchain = 'arbitrum') and from_address in (select address from raw_list where blockchain = 'arbitrum') and block_timestamp::date >= '{{start_date}}' and block_timestamp::date <= '{{end_date}}' group by 1),arbitrum_inflow as ( select symbol, count(distinct origin_from_address) as number_of_users, count(distinct tx_hash) as number_of_tx, sum(amount_usd) as inflow_amount_usd from arbitrum.core.ez_token_transfers where lower(symbol) in (select asset from raw_list where blockchain = 'arbitrum') and to_address in (select address from raw_list where blockchain = 'arbitrum') and block_timestamp::date >= '{{start_date}}' and block_timestamp::date <= '{{end_date}}' group by 1) ,polygon_outflow as ( select symbol, count(distinct origin_from_address) as number_of_users, count(distinct tx_hash) as number_of_tx, sum(amount_usd) as outflow_amount_usd from polygon.core.ez_token_transfers where lower(symbol) in (select asset from raw_list where blockchain = 'polygon') and from_address in (select address from raw_list where blockchain = 'polygon') and block_timestamp::date >= '{{start_date}}' and block_timestamp::date <= '{{end_date}}' group by 1),polygon_inflow as ( select symbol, count(distinct origin_from_address) as number_of_users, count(distinct tx_hash) as number_of_tx, sum(amount_usd) as inflow_amount_usd from polygon.core.ez_token_transfers where lower(symbol) in (select asset from raw_list where blockchain = 'polygon') and to_address in (select address from raw_list where blockchain = 'polygon') and block_timestamp::date >= '{{start_date}}' and block_timestamp::date <= '{{end_date}}' group by 1) ,optimism_outflow as ( select symbol, count(distinct origin_from_address) as number_of_users, count(distinct tx_hash) as number_of_tx, sum(amount_usd) as outflow_amount_usd from optimism.core.ez_token_transfers where lower(symbol) in (select asset from raw_list where blockchain = 'optimism') and from_address in (select address from raw_list where blockchain = 'optimism') and block_timestamp::date >= '{{start_date}}' and block_timestamp::date <= '{{end_date}}' group by 1),optimism_inflow as ( select symbol, count(distinct origin_from_address) as number_of_users, count(distinct tx_hash) as number_of_tx, sum(amount_usd) as inflow_amount_usd from optimism.core.ez_token_transfers where lower(symbol) in (select asset from raw_list where blockchain = 'optimism') and to_address in (select address from raw_list where blockchain = 'optimism') and block_timestamp::date >= '{{start_date}}' and block_timestamp::date <= '{{end_date}}' group by 1),summary_outflow as ( select *,'ethereum' as blockchain from ether_outflow UNION ALL select *,'BSC' as blockchain from bsc_outflow UNION ALL select *,'Polygon' as blockchain from polygon_outflow UNION ALL select *,'Avalanche' as blockchain from avalanche_outflow UNION ALL select *,'Arbitrum' as blockchain from arbitrum_outflow UNION ALL select *,'Optimism' as blockchain from optimism_outflow),summary_inflow as(select *,'ethereum' as blockchain from ether_inflow UNION ALL select *,'BSC' as blockchain from bsc_inflow UNION ALL select *,'Polygon' as blockchain from polygon_inflow UNION ALL select *,'Avalanche' as blockchain from avalanche_inflow UNION ALL select *,'Arbitrum' as blockchain from arbitrum_inflow UNION ALL select *,'Optimism' as blockchain from optimism_inflow),temp as ( select blockchain,symbol,inflow_amount_usd as moved_amount, number_of_tx, 'inflow' as type from summary_inflow union all select blockchain,symbol,outflow_amount_usd as moved_amount, number_of_tx, 'outflow' as type from summary_outflow ) -- ,temp2 as ( -- select blockchain,symbol, sum(inflow_amount_usd+outflow_amount_usd) as moved_amount, sum(number_of_tx) as total_tx from temp -- group by 1,2 -- ) select blockchain,symbol,moved_amount/number_of_tx as average_moved_usd_amount from temp where blockchain = '{{chain}}' and type = 'outflow'

hyoeiseman