-- forked from Sandesh / Cross EVM NFT @ https://flipsidecrypto.xyz/Sandesh/q/4giV3gPpQkyn/cross-evm-nft -- forked from Cross EVM Defi @ https://flipsidecrypto.xyz/edit/queries/42627402-f9de-46e0-b110-7aa88007c632 with acquired_eth_users_nft as ( with acquired_eth_users AS ( select 'ethereum' as chain , from_address, sum(1) as "yes" from ethereum.core.fact_transactions where 1=1 -- and from_address in (lower('0x2c3C48dcFA3875b5D181033A5ffFe1584270462a'),lower('0x4838B106FCe9647Bdf1E7877BF73cE8B0BAD5f97')) and nonce =1 and block_timestamp::date >= '2023-01-01' and block_timestamp::date < '2024-01-01' group by 1,2 ) select distinct from_address,'ethereum' as chain, 1 as "yes" from acquired_eth_users nu inner join ethereum.nft.ez_nft_transfers sw on nu.from_address=sw.NFT_FROM_ADDRESS where sw.block_timestamp::date < '2024-01-01' UNION select distinct from_address,'ethereum' as chain, 1 as "yes" from acquired_eth_users nu inner join ethereum.nft.ez_nft_mints mi on nu.from_address=mi.NFT_TO_ADDRESS where mi.block_timestamp::date < '2024-01-01' UNION select distinct from_address,'ethereum' as chain, 1 as "yes" from acquired_eth_users nu inner join ethereum.nft.ez_nft_sales se on nu.from_address=se.SELLER_ADDRESS where se.block_timestamp::date < '2024-01-01' UNION select distinct from_address,'ethereum' as chain, 1 as "yes" from acquired_eth_users nu inner join ethereum.nft.ez_nft_sales bu on nu.from_address=bu.BUYER_ADDRESS where bu.block_timestamp::date < '2024-01-01' -- group by 1,2,3 -- limit 5000 ), acquired_poly_users_nft as ( with acquired_poly_users AS ( select 'polygon' as chain , from_address, sum(1) as "yes" from polygon.core.fact_transactions where 1=1 -- and from_address in (lower('0x2c3C48dcFA3875b5D181033A5ffFe1584270462a'),lower('0x4838B106FCe9647Bdf1E7877BF73cE8B0BAD5f97')) and nonce =1 and block_timestamp::date >= '2023-01-01' and block_timestamp::date < '2024-01-01' group by 1,2 ) select distinct from_address,'polygon' as chain, 1 as "yes" from acquired_poly_users nu inner join polygon.nft.ez_nft_transfers sw on nu.from_address=sw.NFT_FROM_ADDRESS where sw.block_timestamp::date < '2024-01-01' UNION select distinct from_address,'polygon' as chain, 1 as "yes" from acquired_poly_users nu inner join polygon.nft.ez_nft_mints mi on nu.from_address=mi.NFT_TO_ADDRESS where mi.block_timestamp::date < '2024-01-01' UNION select distinct from_address,'polygon' as chain, 1 as "yes" from acquired_poly_users nu inner join polygon.nft.ez_nft_sales se on nu.from_address=se.SELLER_ADDRESS where se.block_timestamp::date < '2024-01-01' UNION select distinct from_address,'polygon' as chain, 1 as "yes" from acquired_poly_users nu inner join polygon.nft.ez_nft_sales bu on nu.from_address=bu.BUYER_ADDRESS where bu.block_timestamp::date < '2024-01-01' -- group by 1,2,3 -- limit 5000 ), acquired_opti_users_nft as ( with acquired_opti_users AS ( select 'optimism' as chain , from_address, sum(1) as "yes" from optimism.core.fact_transactions where 1=1 -- and from_address in (lower('0x2c3C48dcFA3875b5D181033A5ffFe1584270462a'),lower('0x4838B106FCe9647Bdf1E7877BF73cE8B0BAD5f97')) and nonce =1 and block_timestamp::date >= '2023-01-01' and block_timestamp::date < '2024-01-01' group by 1,2 ) select distinct from_address,'optimism' as chain, 1 as "yes" from acquired_opti_users nu inner join polygon.nft.ez_nft_transfers sw on nu.from_address=sw.NFT_FROM_ADDRESS where sw.block_timestamp::date < '2024-01-01' UNION select distinct from_address,'optimism' as chain, 1 as "yes" from acquired_opti_users nu inner join optimism.nft.ez_nft_sales se on nu.from_address=se.SELLER_ADDRESS where se.block_timestamp::date < '2024-01-01' UNION select distinct from_address,'optimism' as chain, 1 as "yes" from acquired_opti_users nu inner join optimism.nft.ez_nft_sales bu on nu.from_address=bu.BUYER_ADDRESS where bu.block_timestamp::date < '2024-01-01' -- group by 1,2,3 -- limit 5000 ), acquired_arb_users_nft as ( with acquired_arb_users AS ( select 'arbitrum' as chain , from_address, sum(1) as "yes" from arbitrum.core.fact_transactions where 1=1 -- and from_address in (lower('0x2c3C48dcFA3875b5D181033A5ffFe1584270462a'),lower('0x4838B106FCe9647Bdf1E7877BF73cE8B0BAD5f97')) and nonce =1 and block_timestamp::date >= '2023-01-01' and block_timestamp::date < '2024-01-01' group by 1,2 ) select distinct from_address,'arbitrum' as chain, 1 as "yes" from acquired_arb_users nu inner join polygon.nft.ez_nft_transfers sw on nu.from_address=sw.NFT_FROM_ADDRESS where sw.block_timestamp::date < '2024-01-01' UNION select distinct from_address,'arbitrum' as chain, 1 as "yes" from acquired_arb_users nu inner join arbitrum.nft.ez_nft_sales se on nu.from_address=se.SELLER_ADDRESS where se.block_timestamp::date < '2024-01-01' UNION select distinct from_address,'arbitrum' as chain, 1 as "yes" from acquired_arb_users nu inner join arbitrum.nft.ez_nft_sales bu on nu.from_address=bu.BUYER_ADDRESS where bu.block_timestamp::date < '2024-01-01' -- group by 1,2,3 -- limit 5000 ), acquired_avax_users_nft as ( with acquired_avax_users AS ( select 'avalanche' as chain , from_address, sum(1) as "yes" from avalanche.core.fact_transactions where 1=1 -- and from_address in (lower('0x2c3C48dcFA3875b5D181033A5ffFe1584270462a'),lower('0x4838B106FCe9647Bdf1E7877BF73cE8B0BAD5f97')) and nonce =1 and block_timestamp::date >= '2023-01-01' and block_timestamp::date < '2024-01-01' group by 1,2 ) select distinct from_address,'avalanche' as chain, 1 as "yes" from acquired_avax_users nu inner join polygon.nft.ez_nft_transfers sw on nu.from_address=sw.NFT_FROM_ADDRESS where sw.block_timestamp::date < '2024-01-01' UNION select distinct from_address,'avalanche' as chain, 1 as "yes" from acquired_avax_users nu inner join avalanche.nft.ez_nft_sales se on nu.from_address=se.SELLER_ADDRESS where se.block_timestamp::date < '2024-01-01' UNION select distinct from_address,'avalanche' as chain, 1 as "yes" from acquired_avax_users nu inner join avalanche.nft.ez_nft_sales bu on nu.from_address=bu.BUYER_ADDRESS where bu.block_timestamp::date < '2024-01-01' -- group by 1,2,3 -- limit 5000 ), acquired_base_users_nft as ( with acquired_base_users AS ( select 'base' as chain , from_address, sum(1) as "yes" from base.core.fact_transactions where 1=1 -- and from_address in (lower('0x2c3C48dcFA3875b5D181033A5ffFe1584270462a'),lower('0x4838B106FCe9647Bdf1E7877BF73cE8B0BAD5f97')) and nonce =1 and block_timestamp::date >= '2023-01-01' and block_timestamp::date < '2024-01-01' group by 1,2 ) select distinct from_address,'base' as chain, 1 as "yes" from acquired_base_users nu inner join polygon.nft.ez_nft_transfers sw on nu.from_address=sw.NFT_FROM_ADDRESS UNION select distinct from_address,'base' as chain, 1 as "yes" from acquired_base_users nu inner join base.nft.ez_nft_sales se on nu.from_address=se.SELLER_ADDRESS where se.block_timestamp::date < '2024-01-01' UNION select distinct from_address,'base' as chain, 1 as "yes" from acquired_base_users nu inner join base.nft.ez_nft_sales bu on nu.from_address=bu.BUYER_ADDRESS where bu.block_timestamp::date < '2024-01-01' -- group by 1,2,3 -- limit 5000 ), union_ed as ( select distinct from_address, chain, "yes" from acquired_eth_users_nft union all select distinct from_address, chain, "yes" from acquired_poly_users_nft union all select distinct from_address, chain, "yes" from acquired_opti_users_nft union all select distinct from_address, chain, "yes" from acquired_arb_users_nft union all select distinct from_address, chain, "yes" from acquired_avax_users_nft union all select distinct from_address, chain, "yes" from acquired_base_users_nft ), pivoted_table as ( SELECT * FROM ( SELECT from_address, chain, COUNT("yes") as yes_count FROM union_ed GROUP BY from_address, chain ) PIVOT ( MAX(yes_count) FOR chain IN ('ethereum','polygon','optimism','arbitrum','avalanche','base') ) -- where from_address='0xffcd349081ba1369606773b5e44eefa4c1e827d8' ), number_of_chains as (SELECT from_address, coalesce("'ethereum'",0) as "ethereum", coalesce("'polygon'",0) as "polygon", coalesce("'optimism'",0) as "optimism", coalesce("'arbitrum'",0) as "arbitrum", coalesce("'avalanche'",0) as "avalanche", coalesce("'base'",0) as "base", ("ethereum"+"polygon"+"optimism"+"arbitrum"+"avalanche"+"base") as chains FROM pivoted_table) select concat('number of chains :',chains) as chains, count(distinct from_address) as number_of_users from number_of_chains group by chains order by chains

Crypto Users