with -------------------- Arbitrum -------------------- arbitrum_receivers as ( select to_address as address, project_name as name, sum(amount) as volume from arbitrum.core.ez_token_transfers transfers left join arbitrum.core.dim_labels labels on transfers.to_address = labels.address where block_timestamp::date < current_date and contract_address = '0x2297aebd383787a160dd0d9f71508148769342e3' and to_address != '0x0000000000000000000000000000000000000000' group by to_address, name ), arbitrum_senders as ( select from_address as address, project_name as name, sum(amount) as volume from arbitrum.core.ez_token_transfers transfers left join arbitrum.core.dim_labels labels on transfers.from_address = labels.address where block_timestamp::date < current_date and contract_address = '0x2297aebd383787a160dd0d9f71508148769342e3' and from_address != '0x0000000000000000000000000000000000000000' group by from_address, name ), arbitrum_holders as ( select receivers.address, receivers.volume - zeroifnull(senders.volume) as balance from arbitrum_receivers receivers left join arbitrum_senders senders on receivers.address = senders.address ), -------------------- Avalanche -------------------- avalanche_receivers as ( select to_address as address, project_name as name, sum(amount) as volume from avalanche.core.ez_token_transfers transfers left join avalanche.core.dim_labels labels on transfers.to_address = labels.address where block_timestamp::date < current_date and contract_address = '0x152b9d0fdc40c096757f570a51e494bd4b943e50' and to_address != '0x0000000000000000000000000000000000000000' group by to_address, name ), avalanche_senders as ( select from_address as address, project_name as name, sum(amount) as volume from avalanche.core.ez_token_transfers transfers left join avalanche.core.dim_labels labels on transfers.from_address = labels.address where block_timestamp::date < current_date and contract_address = '0x152b9d0fdc40c096757f570a51e494bd4b943e50' and from_address != '0x0000000000000000000000000000000000000000' group by from_address, name ), avalanche_holders as ( select receivers.address, receivers.volume - zeroifnull(senders.volume) as balance from avalanche_receivers receivers left join avalanche_senders senders on receivers.address = senders.address ), -------------------- BSC -------------------- bsc_receivers as ( select to_address as address, project_name as name, sum(amount) as volume from bsc.core.ez_token_transfers transfers left join bsc.core.dim_labels labels on transfers.to_address = labels.address where block_timestamp::date < current_date and contract_address = '0x2297aebd383787a160dd0d9f71508148769342e3' and to_address != '0x0000000000000000000000000000000000000000' group by to_address, name ), bsc_senders as ( select from_address as address, project_name as name, sum(amount) as volume from bsc.core.ez_token_transfers transfers left join bsc.core.dim_labels labels on transfers.from_address = labels.address where block_timestamp::date < current_date and contract_address = '0x2297aebd383787a160dd0d9f71508148769342e3' and from_address != '0x0000000000000000000000000000000000000000' group by from_address, name ), bsc_holders as ( select receivers.address, receivers.volume - zeroifnull(senders.volume) as balance from bsc_receivers receivers left join bsc_senders senders on receivers.address = senders.address ), -------------------- Optimism -------------------- optimism_receivers as ( select to_address as address, project_name as name, sum(amount) as volume from optimism.core.ez_token_transfers transfers left join optimism.core.dim_labels labels on transfers.to_address = labels.address where block_timestamp::date < current_date and contract_address = '0x2297aebd383787a160dd0d9f71508148769342e3' and to_address != '0x0000000000000000000000000000000000000000' group by to_address, name ), optimism_senders as ( select from_address as address, project_name as name, sum(amount) as volume from optimism.core.ez_token_transfers transfers left join optimism.core.dim_labels labels on transfers.from_address = labels.address where block_timestamp::date < current_date and contract_address = '0x2297aebd383787a160dd0d9f71508148769342e3' and from_address != '0x0000000000000000000000000000000000000000' group by from_address, name ), optimism_holders as ( select receivers.address, receivers.volume - zeroifnull(senders.volume) as balance from optimism_receivers receivers left join optimism_senders senders on receivers.address = senders.address ), -------------------- Polygon -------------------- polygon_receivers as ( select to_address as address, project_name as name, sum(amount) as volume from polygon.core.ez_token_transfers transfers left join polygon.core.dim_labels labels on transfers.to_address = labels.address where block_timestamp::date < current_date and contract_address = '0x2297aebd383787a160dd0d9f71508148769342e3' and to_address != '0x0000000000000000000000000000000000000000' group by to_address, name ), polygon_senders as ( select from_address as address, project_name as name, sum(amount) as volume from polygon.core.ez_token_transfers transfers left join polygon.core.dim_labels labels on transfers.from_address = labels.address where block_timestamp::date < current_date and contract_address = '0x2297aebd383787a160dd0d9f71508148769342e3' and from_address != '0x0000000000000000000000000000000000000000' group by from_address, name ), polygon_holders as ( select receivers.address, receivers.volume - zeroifnull(senders.volume) as balance from polygon_receivers receivers left join polygon_senders senders on receivers.address = senders.address ) select 'Arbitrum' as "Blockchain", count(distinct address) as "Holders", avg(balance) as "Average Holding" from arbitrum_holders where balance > 0 union select 'Avalanche' as "Blockchain", count(distinct address) as "Holders", avg(balance) as "Average Holding" from avalanche_holders where balance > 0 union select 'BSC' as "Blockchain", count(distinct address) as "Holders", avg(balance) as "Average Holding" from bsc_holders where balance > 0 union select 'Optimism' as "Blockchain", count(distinct address) as "Holders", avg(balance) as "Average Holding" from optimism_holders where balance > 0 union select 'Polygon' as "Blockchain", count(distinct address) as "Holders", avg(balance) as "Average Holding" from polygon_holders where balance > 0 order by "Blockchain"