select

NFT_COLLECTION,

count (DISTINCT BUYER ) as num_buyers,

count (DISTINCT SELLER ) as num_sellers ,

count (DISTINCT TX_ID ) as sales_volume

from flow.core.fact_nft_sales

where TX_SUCCEEDED = true

group by 1 order by sales_volume DESC limit 5

),

common_buyer_seller as ( select NFT_COLLECTION ,

BUYER ,

SELLER ,

count (DISTINCT tx_id) as num_sales

from flow.core.fact_nft_sales

where NFT_COLLECTION in (select NFT_COLLECTION from Top5 )

-- and num_sales >1

group by 1,2,3 having num_sales>1 order by num_sales desc

),

uncommon_buyer_seller as ( select NFT_COLLECTION ,

BUYER ,

SELLER ,

count (DISTINCT tx_id) as num_sales

from flow.core.fact_nft_sales

where NFT_COLLECTION in (select NFT_COLLECTION from Top5 )

group by 1,2,3 having num_sales=1 order by num_sales desc

),

all_ as (

select 'overlapping buyer/seller' as type , * from common_buyer_seller

UNION ALL

select 'Non-overlapping buyer/seller' as type , * from uncommon_buyer_seller

)

select

(select min(CONTRACT_NAME) from flow.core.dim_contract_labels where EVENT_CONTRACT = NFT_COLLECTION) as NFT_COLLECTION_NAME

, type , sum (num_sales) as sales

from all_