OneDataAnalystTopShot Hold status
    Updated 2022-07-22
    WITH t1 AS (
    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(Balance<=0,'Seller','Holder') AS Hold_status
    FROM t2
    LEFT JOIN t3 ON t2.Buyer = t3.Seller AND t2.Play_type = t3.p2 )

    SELECT Play_type, Hold_status, Count(*) AS Counts
    FROM t4
    Group By 1,2
    Run a query to Download Data