with purchasers as ( select
marketplace,
SELLER PURCHASER
from solana.core.fact_nft_sales
where succeeded = true
and marketplace in ('coral cube','hyperspace')
and BLOCK_TIMESTAMP::date >= CURRENT_DATE - 40
group by 1,2
),
both_ as(
select purchaser ,
count (DISTINCT marketplace) num_marketplace
from purchasers
group by 1 having num_marketplace =2
) ,
only_ as (select
case
when marketplace = 'coral cube' then 'only used coral cube'
when marketplace = 'hyperspace' then 'only used hyperspace'
else 'kir'
end type ,
count (DISTINCT PURCHASER) num_sellers
from purchasers
where PURCHASER not in (select purchaser from both_ )
group by 1
)
select 'used both' type , count (purchaser) num_sellers from both_
UNION
select type , num_sellers from only_