distinct BUYER as u1,

BLOCK_TIMESTAMP as buy_time

from flow.core.fact_nft_sales

where TX_SUCCEEDED='TRUE'

and tx_id is not NULL

),

tb2 as (select

distinct seller as u2,

BLOCK_TIMESTAMP as sell_time

from flow.core.fact_nft_sales

where TX_SUCCEEDED='TRUE'

and tx_id is not NULL

),

tb3 as (select

tb1.u1 as users,

Avg(abs(DATEDIFF(day, buy_time,sell_time))) as hold_time

from tb1 join tb2 on tb1.u1=tb2.u2 group by 1)

case

when hold_time < 2 then 'under 2 days'

when hold_time between 2 and 7 then '2-7 days'

when hold_time between 7 and 30 then '7-30 days'

when hold_time>=30 then 'more than month'

end as gp,

count(distinct users)

from tb3

group by 1 having gp is not null