select NFT_ID,PLAY_TYPE,NFT_COLLECTION,DATEDIFF('day',day,next_day::date) as hold_duration,PRICE,CURRENCY from

(

select s.NFT_ID

,m.PLAY_TYPE

,s.NFT_COLLECTION

,s.BLOCK_TIMESTAMP::date as day

,case when lead(s.BLOCK_TIMESTAMP) over (partition by s.NFT_ID order by s.BLOCK_TIMESTAMP ASC) is null then

current_date

ELSE

lead(s.BLOCK_TIMESTAMP) over (partition by s.NFT_ID order by s.BLOCK_TIMESTAMP )

END as next_day

,s.PRICE

,s.CURRENCY

FROM flow.core.fact_nft_sales s

JOIN flow.core.dim_topshot_metadata m

ON s.NFT_ID=m.NFT_ID

where s.TX_SUCCEEDED=TRUE

)

select PLAY_TYPE, hold_duration ,count(*) as cnt

from NFT_Sale

where PLAY_TYPE='Assist'

group by PLAY_TYPE, hold_duration

order by PLAY_TYPE,hold_duration