-- forked from Vamp-2819 / USD inflow/outflow @ https://flipsidecrypto.xyz/Vamp-2819/q/rRfcnaDmCET5/usd-inflow-outflow ----------------- Ethereum ----------------- with eth_layer0 as ( select address from ethereum.core.dim_labels where address_name ilike '%layerzero%' or address_name ilike '%layer zero%' ), eth_inflow as ( select 'Ethereum' as chain, date_trunc('{{timeframe}}', block_timestamp) as time, count(DISTINCT(tx_hash)) as tx_inflow, count(DISTINCT(from_address)) as users_inflow, sum(amount_usd) as volume_inflow from ethereum.core.ez_token_transfers where to_address in (select address from eth_layer0 ) and amount_usd is not null and time >= '{{start_date}}' and time <= '{{end_date}}' group by 1,2 ), eth_outflow as ( select 'Ethereum' as chain, date_trunc('{{timeframe}}', block_timestamp) as time, count(DISTINCT(tx_hash)) as tx_outflow, count(DISTINCT(to_address)) as users_outflow, sum(amount_usd) as volume_outflow from ethereum.core.ez_token_transfers where from_address in (select address from eth_layer0 ) and amount_usd is not null and time >= '{{start_date}}' and time <= '{{end_date}}' group by 1,2 ), eth_netflow as ( select chain, time, i.tx_inflow as inflow_transactions, o.tx_outflow as outflow_transactions, i.users_inflow as inflow_users, o.users_outflow as outflow_users, i.volume_inflow as volume_inflow_in_usd, o.volume_outflow as volume_outflow_in_usd, i.volume_inflow - o.volume_outflow as netflow from eth_inflow i join eth_outflow o using(time) ), ----------------- Arbitrum ----------------- arb_layer0 as ( select address from arbitrum.core.dim_labels where address_name ilike '%layerzero%' or address_name ilike '%layer zero%' ), arb_inflow as ( select 'Arbitrum' as chain, date_trunc('{{timeframe}}', block_timestamp) as time, count(DISTINCT(tx_hash)) as tx_inflow, count(DISTINCT(from_address)) as users_inflow, sum(amount_usd) as volume_inflow from arbitrum.core.ez_token_transfers where to_address in (select address from arb_layer0 ) and amount_usd is not null and time >= '{{start_date}}' and time <= '{{end_date}}' group by 1,2 ), arb_outflow as ( select 'Arbitrum' as chain, date_trunc('{{timeframe}}', block_timestamp) as time, count(DISTINCT(tx_hash)) as tx_outflow, count(DISTINCT(to_address)) as users_outflow, sum(amount_usd) as volume_outflow from arbitrum.core.ez_token_transfers where from_address in (select address from arb_layer0 ) and amount_usd is not null and time >= '{{start_date}}' and time <= '{{end_date}}' group by 1,2 ), arb_netflow as ( select chain, time, i.tx_inflow as inflow_transactions, o.tx_outflow as outflow_transactions, i.users_inflow as inflow_users, o.users_outflow as outflow_users, i.volume_inflow as volume_inflow_in_usd, o.volume_outflow as volume_outflow_in_usd, i.volume_inflow - o.volume_outflow as netflow from arb_inflow i join arb_outflow o using(time) ), ----------------- Avalanche ----------------- avax_layer0 as ( select address from avalanche.core.dim_labels where address_name ilike '%layerzero%' or address_name ilike '%layer zero%' ), avax_inflow as ( select 'Avalanche' as chain, date_trunc('{{timeframe}}', block_timestamp) as time, count(DISTINCT(tx_hash)) as tx_inflow, count(DISTINCT(from_address)) as users_inflow, sum(amount_usd) as volume_inflow from avalanche.core.ez_token_transfers where to_address in (select address from avax_layer0 ) and amount_usd is not null and time >= '{{start_date}}' and time <= '{{end_date}}' group by 1,2 ), avax_outflow as ( select 'Avalanche' as chain, date_trunc('{{timeframe}}', block_timestamp) as time, count(DISTINCT(tx_hash)) as tx_outflow, count(DISTINCT(to_address)) as users_outflow, sum(amount_usd) as volume_outflow from avalanche.core.ez_token_transfers where from_address in (select address from avax_layer0 ) and amount_usd is not null and time >= '{{start_date}}' and time <= '{{end_date}}' group by 1,2 ), avax_netflow as ( select chain, time, i.tx_inflow as inflow_transactions, o.tx_outflow as outflow_transactions, i.users_inflow as inflow_users, o.users_outflow as outflow_users, i.volume_inflow as volume_inflow_in_usd, o.volume_outflow as volume_outflow_in_usd, volume_inflow - volume_outflow as netflow from avax_inflow i join avax_outflow o using(time) ), ----------------- Optimism ----------------- opt_layer0 as ( select address from optimism.core.dim_labels where address_name ilike '%layerzero%' or address_name ilike '%layer zero%' ), opt_inflow as ( select 'Optimism' as chain, date_trunc('{{timeframe}}', block_timestamp) as time, count(DISTINCT(tx_hash)) as tx_inflow, count(DISTINCT(from_address)) as users_inflow, sum(amount_usd) as volume_inflow from optimism.core.ez_token_transfers where to_address in (select address from opt_layer0 ) and amount_usd is not null and time >= '{{start_date}}' and time <= '{{end_date}}' group by 1,2 ), opt_outflow as ( select 'Optimism' as chain, date_trunc('{{timeframe}}', block_timestamp) as time, count(DISTINCT(tx_hash)) as tx_outflow, count(DISTINCT(to_address)) as users_outflow, sum(amount_usd) as volume_outflow from optimism.core.ez_token_transfers where from_address in (select address from opt_layer0 ) and amount_usd is not null and time >= '{{start_date}}' and time <= '{{end_date}}' group by 1,2 ), opt_netflow as ( select chain, time, i.tx_inflow as inflow_transactions, o.tx_outflow as outflow_transactions, i.users_inflow as inflow_users, o.users_outflow as outflow_users, i.volume_inflow as volume_inflow_in_usd, o.volume_outflow as volume_outflow_in_usd, volume_inflow - volume_outflow as netflow from opt_inflow i join opt_outflow o using(time) ), ----------------- BSC ----------------- bsc_layer0 as ( select address from bsc.core.dim_labels where address_name ilike '%layerzero%' or address_name ilike '%layer zero%' ), bsc_inflow as ( select 'BSC' as chain, date_trunc('{{timeframe}}', block_timestamp) as time, count(DISTINCT(tx_hash)) as tx_inflow, count(DISTINCT(from_address)) as users_inflow, sum(amount_usd) as volume_inflow from bsc.core.ez_token_transfers where to_address in (select address from bsc_layer0 ) and amount_usd is not null and time >= '{{start_date}}' and time <= '{{end_date}}' group by 1,2 ), bsc_outflow as ( select 'BSC' as chain, date_trunc('{{timeframe}}', block_timestamp) as time, count(DISTINCT(tx_hash)) as tx_outflow, count(DISTINCT(to_address)) as users_outflow, sum(amount_usd) as volume_outflow from bsc.core.ez_token_transfers where from_address in (select address from bsc_layer0 ) and amount_usd is not null and time >= '{{start_date}}' and time <= '{{end_date}}' group by 1,2 ), bsc_netflow as ( select chain, time, i.tx_inflow as inflow_transactions, o.tx_outflow as outflow_transactions, i.users_inflow as inflow_users, o.users_outflow as outflow_users, i.volume_inflow as volume_inflow_in_usd, o.volume_outflow as volume_outflow_in_usd, volume_inflow - volume_outflow as netflow from bsc_inflow i join bsc_outflow o using(time) ), ----------------- Polygon ----------------- matic_layer0 as ( select address from polygon.core.dim_labels where address_name ilike '%layerzero%' or address_name ilike '%layer zero%' or project_name ilike '%layerzero%' ), matic_inflow as ( select 'Polygon' as chain, date_trunc('{{timeframe}}', block_timestamp) as time, count(DISTINCT(tx_hash)) as tx_inflow, count(DISTINCT(from_address)) as users_inflow, sum(amount_usd) as volume_inflow from polygon.core.ez_token_transfers where to_address in (select address from matic_layer0 ) and amount_usd is not null and time >= '{{start_date}}' and time <= '{{end_date}}' group by 1,2 ), matic_outflow as ( select 'Polygon' as chain, date_trunc('{{timeframe}}', block_timestamp) as time, count(DISTINCT(tx_hash)) as tx_outflow, count(DISTINCT(to_address)) as users_outflow, sum(amount_usd) as volume_outflow from polygon.core.ez_token_transfers where from_address in (select address from matic_layer0 ) and amount_usd is not null and time >= '{{start_date}}' and time <= '{{end_date}}' group by 1,2 ), matic_netflow as ( select chain, time, i.tx_inflow as inflow_transactions, o.tx_outflow as outflow_transactions, i.users_inflow as inflow_users, o.users_outflow as outflow_users, i.volume_inflow as volume_inflow_in_usd, o.volume_outflow as volume_outflow_in_usd, volume_inflow - volume_outflow as netflow from matic_inflow i join matic_outflow o using(time) ) select * from eth_netflow union select * from arb_netflow union select * from avax_netflow union select * from opt_netflow union select * from bsc_netflow union select * from matic_netflow

Query	Creator	Created
MAGIC Token Price on Arbitrum arbitrum l2 evm	mixmarveldaoventure	Jul 5, 2023
LayerZero Users interactions copy arbitrum ethereum optimism avalanche polygon	mixmarveldaoventure	Jun 29, 2023
USD inflow/outflow copy bsc arbitrum ethereum optimism avalanche	mixmarveldaoventure	Jun 29, 2023

mixmarveldaoventure