DISTINCT(event_data:id) as nft_id,

event_data:to as holder,

MAX(block_timestamp) as times

from flow.core.fact_events

WHERE EVENT_TYPE='Deposit' and EVENT_CONTRACT='A.0b2a3299cc857e29.TopShot'

GROUP by nft_id,holder),

SELECT_main as (

select DISTINCT(nft_id),max(TIMES) as times

from main

GROUP by NFT_ID

ORDER by nft_id desc

),

currect_main as (

select SELECT_main.nft_id,holder,SELECT_main.times

from(SELECT_main INNER JOIN main ON SELECT_main.NFT_ID = main.NFT_ID and SELECT_main.times=main.times)

ORDER by nft_id desc),

main_join as(

select flow.core.dim_topshot_metadata.NFT_ID,holder,currect_main.times,PLAY_TYPE

from(currect_main INNER JOIN flow.core.dim_topshot_metadata ON currect_main.NFT_ID = flow.core.dim_topshot_metadata.NFT_ID )

select COUNT(DISTINCT(holder)) as number_of_holders,COUNT(DISTINCT(nft_id)) as number_of_nfts,PLAY_TYPE,

number_of_nfts/number_of_holders as "Average NFT per person"

from main_join

WHERE PLAY_TYPE='Rim'

GROUP by PLAY_TYPE

select COUNT(DISTINCT(holder)) as number_of_holders,COUNT(DISTINCT(nft_id)) as number_of_nfts,PLAY_TYPE,

number_of_nfts/number_of_holders as "Average NFT per person"

from main_join

WHERE PLAY_TYPE='2 Pointer'

GROUP by PLAY_TYPE

select COUNT(DISTINCT(holder)) as number_of_holders,COUNT(DISTINCT(nft_id)) as number_of_nfts,PLAY_TYPE,

number_of_nfts/number_of_holders as "Average NFT per person"

from main_join