select

buyer,

SPLIT(NFT_COLLECTION, '.')[2] as NFT_collection

from flow.core.fact_nft_sales

where block_timestamp >= '2022-04-01'

and SPLIT(NFT_COLLECTION, '.')[2] = 'TopShot'

group by NFT_collection,1

),

table1 as(

select

count(distinct(NFT_ID)) as n_NFT,

buyer,

case

when n_NFT <= 10 then 'Crab'

when n_NFT <=100 and n_NFT > 10 then 'Shark'

else 'Whale'

end as Tier

from flow.core.fact_nft_sales

group by 2

)

select

tier,

sum(n_NFT),

count(distinct(buyer)) as n_buyers

from table1

group by 1