with arbitrum_inflows as ( select trunc(block_timestamp,'{{granularity}}') as date,x.symbol, count(distinct tx_hash) as transactions, count(distinct origin_from_address) as active_users, sum(amount_usd) as volume, avg(amount_usd) as avg_volume from arbitrum.core.ez_token_transfers x join arbitrum.core.dim_labels z on x.to_address=z.address where amount_usd<1e9 and block_timestamp >=CURRENT_DATE-INTERVAL '1 MONTH' and project_name ilike '%multichain%' group by 1,2 UNION select trunc(block_timestamp,'{{granularity}}') as date,'ETH' as symbol, count(distinct tx_hash) as transactions, count(distinct origin_from_address) as active_users, sum(amount_usd) as volume, avg(amount_usd) as avg_volume from arbitrum.core.ez_eth_transfers x join arbitrum.core.dim_labels z on x.eth_to_address=z.address where amount_usd<1e9 and block_timestamp >=CURRENT_DATE-INTERVAL '1 MONTH' and project_name ilike '%multichain%' group by 1,2 ), arbitrum_outflows as ( select trunc(block_timestamp,'{{granularity}}') as date,x.symbol, count(distinct tx_hash) as transactions, count(distinct to_address) as active_users, sum(amount_usd) as volume, avg(amount_usd) as avg_volume from arbitrum.core.ez_token_transfers x join arbitrum.core.dim_labels z on x.from_address=z.address where amount_usd<1e9 and block_timestamp >=CURRENT_DATE-INTERVAL '1 MONTH' and project_name ilike '%multichain%' group by 1,2 UNION select trunc(block_timestamp,'{{granularity}}') as date,'ETH' as symbol, count(distinct tx_hash) as transactions, count(distinct origin_to_address) as active_users, sum(amount_usd) as volume, avg(amount_usd) as avg_volume from arbitrum.core.ez_eth_transfers x join arbitrum.core.dim_labels z on x.eth_from_address=z.address where amount_usd<1e9 and block_timestamp >=CURRENT_DATE-INTERVAL '1 MONTH' and project_name ilike '%multichain%' group by 1,2 ), totals_arb as ( select ifnull(x.date,y.date) as date,ifnull(x.symbol,y.symbol) as symbol, 'Arbitrum' as chain, x.transactions as transactions_in,y.transactions as transactions_out, x.active_users as active_depositors,y.active_users as active_withdrawers, x.volume as volume_in,y.volume as volume_out, x.avg_volume as avg_volume_in, y.avg_volume as avg_volume_out from arbitrum_inflows x join arbitrum_outflows y on x.date=y.date and x.symbol=y.symbol order by 1 asc,2 ), avalanche_inflows as ( select trunc(block_timestamp,'{{granularity}}') as date,x.symbol, count(distinct tx_hash) as transactions, count(distinct origin_from_address) as active_users, sum(amount_usd) as volume, avg(amount_usd) as avg_volume from avalanche.core.ez_token_transfers x join avalanche.core.dim_labels z on x.to_address=z.address where amount_usd<1e9 and block_timestamp >=CURRENT_DATE-INTERVAL '1 MONTH' and project_name ilike '%multichain%' group by 1,2 UNION select trunc(block_timestamp,'{{granularity}}') as date,'AVAX' as symbol, count(distinct tx_hash) as transactions, count(distinct origin_from_address) as active_users, sum(amount_usd) as volume, avg(amount_usd) as avg_volume from avalanche.core.ez_avax_transfers x join avalanche.core.dim_labels z on x.avax_to_address=z.address where amount_usd<1e9 and block_timestamp >=CURRENT_DATE-INTERVAL '1 MONTH' and project_name ilike '%multichain%' group by 1,2 ), avalanche_outflows as ( select trunc(block_timestamp,'{{granularity}}') as date,x.symbol, count(distinct tx_hash) as transactions, count(distinct to_address) as active_users, sum(amount_usd) as volume, avg(amount_usd) as avg_volume from avalanche.core.ez_token_transfers x join avalanche.core.dim_labels z on x.from_address=z.address where amount_usd<1e9 and block_timestamp >=CURRENT_DATE-INTERVAL '1 MONTH' and project_name ilike '%multichain%' group by 1,2 UNION select trunc(block_timestamp,'{{granularity}}') as date,'AVAX' as symbol, count(distinct tx_hash) as transactions, count(distinct origin_to_address) as active_users, sum(amount_usd) as volume, avg(amount_usd) as avg_volume from avalanche.core.ez_avax_transfers x join avalanche.core.dim_labels z on x.avax_from_address=z.address where amount_usd<1e9 and block_timestamp >=CURRENT_DATE-INTERVAL '1 MONTH' and project_name ilike '%multichain%' group by 1,2 ), totals_avax as ( select ifnull(x.date,y.date) as date,ifnull(x.symbol,y.symbol) as symbol, 'Avalanche' as chain, x.transactions as transactions_in,y.transactions as transactions_out, x.active_users as active_depositors,y.active_users as active_withdrawers, x.volume as volume_in,y.volume as volume_out, x.avg_volume as avg_volume_in, y.avg_volume as avg_volume_out from avalanche_inflows x join avalanche_outflows y on x.date=y.date and x.symbol=y.symbol order by 1 asc,2 ), bsc_inflows as ( select trunc(block_timestamp,'{{granularity}}') as date,x.symbol, count(distinct tx_hash) as transactions, count(distinct origin_from_address) as active_users, sum(amount_usd) as volume, avg(amount_usd) as avg_volume from bsc.core.ez_token_transfers x join bsc.core.fact_hourly_token_prices y join bsc.core.dim_labels z on x.to_address=z.address where amount_usd<1e9 and block_timestamp >=CURRENT_DATE-INTERVAL '1 MONTH' and project_name ilike '%multichain%' group by 1,2 UNION select trunc(block_timestamp,'{{granularity}}') as date,'BNB' as symbol, count(distinct tx_hash) as transactions, count(distinct origin_from_address) as active_users, sum(amount_usd) as volume, avg(amount_usd) as avg_volume from bsc.core.ez_bnb_transfers x join bsc.core.dim_labels z on x.bnb_to_address=z.address where amount_usd<1e9 and block_timestamp >=CURRENT_DATE-INTERVAL '1 MONTH' and project_name ilike '%multichain%' group by 1,2 ), bsc_outflows as ( select trunc(block_timestamp,'{{granularity}}') as date,x.symbol, count(distinct tx_hash) as transactions, count(distinct to_address) as active_users, sum(amount_usd) as volume, avg(amount_usd) as avg_volume from bsc.core.ez_token_transfers x join bsc.core.dim_labels z on x.from_address=z.address where amount_usd<1e9 and block_timestamp >=CURRENT_DATE-INTERVAL '1 MONTH' and project_name ilike '%multichain%' group by 1,2 UNION select trunc(block_timestamp,'{{granularity}}') as date,'BNB' as symbol, count(distinct tx_hash) as transactions, count(distinct origin_to_address) as active_users, sum(amount_usd) as volume, avg(amount_usd) as avg_volume from bsc.core.ez_bnb_transfers x join bsc.core.dim_labels z on x.origin_from_address=z.address where amount_usd<1e9 and block_timestamp >=CURRENT_DATE-INTERVAL '1 MONTH' and project_name ilike '%multichain%' group by 1,2 ), totals_bsc as ( select ifnull(x.date,y.date) as date,ifnull(x.symbol,y.symbol) as symbol, 'BSC' as chain, x.transactions as transactions_in,y.transactions as transactions_out, x.active_users as active_depositors,y.active_users as active_withdrawers, x.volume as volume_in,y.volume as volume_out, x.avg_volume as avg_volume_in, y.avg_volume as avg_volume_out from bsc_inflows x join bsc_outflows y on x.date=y.date and x.symbol=y.symbol order by 1 asc,2 ), ethereum_inflows as ( select trunc(block_timestamp,'{{granularity}}') as date,x.symbol, count(distinct tx_hash) as transactions, count(distinct origin_from_address) as active_users, sum(amount_usd) as volume, avg(amount_usd) as avg_volume from ethereum.core.ez_token_transfers x join ethereum.core.dim_labels z on x.to_address=z.address where amount_usd<1e9 and block_timestamp >=CURRENT_DATE-INTERVAL '1 MONTH' and label ilike '%multichain%' group by 1,2 UNION select trunc(block_timestamp,'{{granularity}}') as date,'ETH' as symbol, count(distinct tx_hash) as transactions, count(distinct origin_from_address) as active_users, sum(amount_usd) as volume, avg(amount_usd) as avg_volume from ethereum.core.ez_eth_transfers x join ethereum.core.dim_labels z on x.eth_to_address=z.address where amount_usd<1e9 and block_timestamp >=CURRENT_DATE-INTERVAL '1 MONTH' and label ilike '%multichain%' group by 1,2 ), ethereum_outflows as ( select trunc(block_timestamp,'{{granularity}}') as date,x.symbol, count(distinct tx_hash) as transactions, count(distinct to_address) as active_users, sum(amount_usd) as volume, avg(amount_usd) as avg_volume from ethereum.core.ez_token_transfers x join ethereum.core.dim_labels z on x.from_address=z.address where amount_usd<1e9 and block_timestamp >=CURRENT_DATE-INTERVAL '1 MONTH' and label ilike '%multichain%' group by 1,2 UNION select trunc(block_timestamp,'{{granularity}}') as date,'ETH' as symbol, count(distinct tx_hash) as transactions, count(distinct origin_to_address) as active_users, sum(amount_usd) as volume, avg(amount_usd) as avg_volume from ethereum.core.ez_eth_transfers x join ethereum.core.dim_labels z on x.eth_from_address=z.address where amount_usd<1e9 and block_timestamp >=CURRENT_DATE-INTERVAL '1 MONTH' and label ilike '%multichain%' group by 1,2 ), totals_eth as ( select ifnull(x.date,y.date) as date,ifnull(x.symbol,y.symbol) as symbol, 'Ethereum' as chain, x.transactions as transactions_in,y.transactions as transactions_out, x.active_users as active_depositors,y.active_users as active_withdrawers, x.volume as volume_in,y.volume as volume_out, x.avg_volume as avg_volume_in, y.avg_volume as avg_volume_out from ethereum_inflows x join ethereum_outflows y on x.date=y.date and x.symbol=y.symbol order by 1 asc,2 ), optimism_inflows as ( select trunc(block_timestamp,'{{granularity}}') as date,x.symbol, count(distinct tx_hash) as transactions, count(distinct origin_from_address) as active_users, sum(amount_usd) as volume, avg(amount_usd) as avg_volume from optimism.core.ez_token_transfers x join optimism.core.dim_labels z on x.to_address=z.address where amount_usd<1e9 and block_timestamp >=CURRENT_DATE-INTERVAL '1 MONTH' and project_name ilike '%multichain%' group by 1,2 UNION select trunc(block_timestamp,'{{granularity}}') as date,'ETH' as symbol, count(distinct tx_hash) as transactions, count(distinct origin_from_address) as active_users, sum(amount_usd) as volume, avg(amount_usd) as avg_volume from optimism.core.ez_eth_transfers x join optimism.core.dim_labels z on x.eth_to_address=z.address where amount_usd<1e9 and block_timestamp >=CURRENT_DATE-INTERVAL '1 MONTH' and project_name ilike '%multichain%' group by 1,2 ), optimism_outflows as ( select trunc(block_timestamp,'{{granularity}}') as date,x.symbol, count(distinct tx_hash) as transactions, count(distinct to_address) as active_users, sum(amount_usd) as volume, avg(amount_usd) as avg_volume from optimism.core.ez_token_transfers x join optimism.core.dim_labels z on x.from_address=z.address where amount_usd<1e9 and block_timestamp >=CURRENT_DATE-INTERVAL '1 MONTH' and project_name ilike '%multichain%' group by 1,2 UNION select trunc(block_timestamp,'{{granularity}}') as date,'ETH' as symbol, count(distinct tx_hash) as transactions, count(distinct origin_to_address) as active_users, sum(amount_usd) as volume, avg(amount_usd) as avg_volume from optimism.core.ez_eth_transfers x join optimism.core.dim_labels z on x.eth_from_address=z.address where amount_usd<1e9 and block_timestamp >=CURRENT_DATE-INTERVAL '1 MONTH' and project_name ilike '%multichain%' group by 1,2 ), totals_opt as ( select ifnull(x.date,y.date) as date,ifnull(x.symbol,y.symbol) as symbol, 'Optimism' as chain, x.transactions as transactions_in,y.transactions as transactions_out, x.active_users as active_depositors,y.active_users as active_withdrawers, x.volume as volume_in,y.volume as volume_out, x.avg_volume as avg_volume_in, y.avg_volume as avg_volume_out from optimism_inflows x join optimism_outflows y on x.date=y.date and x.symbol=y.symbol order by 1 asc,2 ), polygon_inflows as ( select trunc(block_timestamp,'{{granularity}}') as date,x.symbol, count(distinct tx_hash) as transactions, count(distinct origin_from_address) as active_users, sum(amount_usd) as volume, avg(amount_usd) as avg_volume from polygon.core.ez_token_transfers x join polygon.core.dim_labels z on x.to_address=z.address where amount_usd<1e9 and block_timestamp >=CURRENT_DATE-INTERVAL '1 MONTH' and project_name ilike '%multichain%' group by 1,2 UNION select trunc(block_timestamp,'{{granularity}}') as date,'MATIC' as symbol, count(distinct tx_hash) as transactions, count(distinct origin_from_address) as active_users, sum(amount_usd) as volume, avg(amount_usd) as avg_volume from polygon.core.ez_matic_transfers x join polygon.core.dim_labels z on x.matic_to_address=z.address where amount_usd<1e9 and block_timestamp >=CURRENT_DATE-INTERVAL '1 MONTH' and project_name ilike '%multichain%' group by 1,2 ), polygon_outflows as ( select trunc(block_timestamp,'{{granularity}}') as date,x.symbol, count(distinct tx_hash) as transactions, count(distinct to_address) as active_users, sum(amount_usd) as volume, avg(amount_usd) as avg_volume from polygon.core.ez_token_transfers x join polygon.core.dim_labels z on x.from_address=z.address where amount_usd<1e9 and block_timestamp >=CURRENT_DATE-INTERVAL '1 MONTH' and project_name ilike '%multichain%' group by 1,2 UNION select trunc(block_timestamp,'{{granularity}}') as date,'MATIC' as symbol, count(distinct tx_hash) as transactions, count(distinct origin_to_address) as active_users, sum(amount_usd) as volume, avg(amount_usd) as avg_volume from polygon.core.ez_matic_transfers x join polygon.core.dim_labels z on x.matic_from_address=z.address where amount_usd<1e9 and block_timestamp >=CURRENT_DATE-INTERVAL '1 MONTH' and project_name ilike '%multichain%' group by 1,2 ), totals_poly as ( select ifnull(x.date,y.date) as date,ifnull(x.symbol,y.symbol) as symbol, 'Polygon' as chain, x.transactions as transactions_in,y.transactions as transactions_out, x.active_users as active_depositors,y.active_users as active_withdrawers, x.volume as volume_in,y.volume as volume_out, x.avg_volume as avg_volume_in, y.avg_volume as avg_volume_out from polygon_inflows x join polygon_outflows y on x.date=y.date and x.symbol=y.symbol order by 1 asc,2 ), totals as ( select * from totals_arb union select * from totals_avax union select * from totals_bsc union select * from totals_eth union select * from totals_opt union select * from totals_poly ) select date, case when date>='2023-05-25' then 'After expected upgrade' else 'Before expected upgrade' end as period, sum(transactions_in) as transactions_inflow, sum(transactions_out) as transactions_outflow, transactions_inflow-transactions_outflow as netflow_transactions, sum(active_depositors) as active_depositors, sum(active_withdrawers) as active_withdrawers, sum(volume_in) as volume_inflow, sum(volume_out) as volume_outflow, volume_inflow-volume_outflow as netflow_volume, avg(avg_volume_in) as avg_volume_in,avg(avg_volume_out) as avg_volume_out from totals where chain ='{{chain}}' group by 1,2 order by 1

Multichain perfomance after no-upgrade