select

play_type,

EVENT_TYPE,

EVENT_DATA ,

b.nft_id

from flow.core.fact_events a join flow.core.dim_topshot_metadata b on a.EVENT_DATA:id = b.nft_id

where EVENT_TYPE in ( 'Withdraw' , 'Deposit')

and tx_succeeded = 'TRUE'

, buyer as (

select

EVENT_DATA:to as buyers,

play_type,

nft_id

from base

where EVENT_TYPE ='Deposit'

)

, seller as (

select

EVENT_DATA:from as sellers,

play_type,

nft_id

from base

where EVENT_TYPE ='Withdraw'

)

select

a.play_type,

COUNT(distinct buyers) - count(distinct sellers) as holders ,

row_number()over(order by holders desc ) as n

from buyer a

left join seller b on a.buyers = b.sellers

and a.nft_id = b.nft_id

group by 1

order by 2 desc