-- 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

mixmarveldaoventure