select NFT_ID,PLAY_TYPE,DATEDIFF('day',buy_day,CURRENT_DATE) as hold_duration from

(

select s.NFT_ID

,m.PLAY_TYPE

, max(s.BLOCK_TIMESTAMP::date ) as buy_day

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

GROUP by s.NFT_ID,m.PLAY_TYPE

)

select PLAY_TYPE

, avg(hold_duration) as avg_hold_duration

from NFT_Sale

group by PLAY_TYPE

order by 2 desc