SELECT Buyer, Seller, Play_type

FROM flow.core.fact_nft_sales

JOIN flow.core.dim_topshot_metadata ON flow.core.fact_nft_sales.NFT_ID = flow.core.dim_topshot_metadata.NFT_ID

WHERE flow.core.fact_nft_sales.NFT_COLLECTION = 'A.0b2a3299cc857e29.TopShot'

),

t2 AS(

SELECT Buyer, Play_type , Count(*) AS Buys

FROM t1

Group By 1,2

),

t3 AS(

SELECT Seller, Play_type As p2, Count(*) AS sells

FROM t1

Group By 1,2

),

t4 AS(

Select Buyer as Collector, Play_type, (buys-sells) AS Balance,

IFF(Sells>0.65 * Buys and buys>=3,'Not-Holder',IFF(Balance<=0,'Not-Holder','Holder')) AS Hold_status

FROM t2

LEFT JOIN t3 ON t2.Buyer = t3.Seller AND t2.Play_type = t3.p2 ),

t5 AS(

SELECT Play_type, Hold_status, Count(*) AS Counts

FROM t4

Group By 1,2 )

SELECT play_type, (counts / (counts + NOTHolder_counts))*100 AS holder_percent,Total_supply_percent

from t5

JOIN ( SELECT play_type as p2, counts AS NOTHolder_counts from t5 where HOLD_STATUS= 'Not-Holder' ) nh