min(BLOCK_TIMESTAMP) as first_buy,

buyer

from flow.core.ez_nft_sales

where TX_SUCCEEDED='TRUE'

and tx_id is not NULL

and NFT_COLLECTION ilike '%topshot%'

group by 2)

,b as (

select

max(BLOCK_TIMESTAMP) as last_sell,

SELLER

from flow.core.ez_nft_sales

where TX_SUCCEEDED='TRUE'

and tx_id is not NULL

and seller in (select buyer from a)

group by 2

)

,c as (

select

b.seller,

avg(DATEDIFF(day,first_buy,last_sell)) as avg_hold_days

from a,b where last_sell>first_buy

group by 1

select

case

when avg_hold_days > 0 and avg_hold_days <= 10 then '1-10 days'

when avg_hold_days > 10 and avg_hold_days <= 20 then '11-20 days'

when avg_hold_days > 20 and avg_hold_days <= 30 then '21-30 days'

when avg_hold_days > 30 and avg_hold_days <= 60 then '31-60 days'

when avg_hold_days > 60 and avg_hold_days <= 90 then '61-90 days'

when avg_hold_days > 90 then 'over 3 months'

when avg_hold_days = 0 then 'less than 24 hours'