from flow.core.fact_nft_sales),

tab2 as (select seller, block_timestamp::date as sales_date, nft_collection, nft_id

from flow.core.fact_nft_sales)

select buyer as address, datediff('day',purchase_date,sales_date) as hold_time, case

when hold_time<1 then 'Less than a day'

when hold_time=1 then 'One day later'

when hold_time>1 and hold_time<=7 then 'More than a day and less than a week'

when hold_time>7 and hold_time<=30 then 'More than a week and less than a month'

when hold_time>30 then 'More than a month'

end as hold_duration

from tab1 left join tab2

on tab1.nft_collection=tab2.nft_collection and tab1.nft_id=tab2.nft_id and tab1.buyer=tab2.seller

where purchase_date is not NULL

and sales_date is not NULL)

select hold_duration, count(address)

from table1

group by 1