distinct BUYER as u1,

min(BLOCK_TIMESTAMP) as x1

FROM flow.core.fact_nft_sales

where CURRENCY='A.ead892083b3e2c6c.DapperUtilityCoin'

and BLOCK_TIMESTAMP>='2022-01-01'

group by 1),

tb2 as (SELECT

distinct SELLER as u2,

min(BLOCK_TIMESTAMP) as x2

FROM flow.core.fact_nft_sales

where CURRENCY='A.ead892083b3e2c6c.DapperUtilityCoin'

and BLOCK_TIMESTAMP>='2022-05-01'

and SELLER in (select u1 from tb1)

group by 1) ,

tb3 as (select

tb1.u1 as purchaser,

avg(DATEDIFF(day,x1, x2 )) as time_between

from tb1

join tb2 on tb1.u1=tb2.u2

group by 1)

select

case when time_between<1 then 'whitin 24 Hours'

when time_between>=1 and time_between<7 then 'Under 1 week'

when time_between>=7 and time_between<30 then 'Between 1 week to 1 month'

when time_between>=30 then 'Over 1 month'

end as buckets,

count(distinct purchaser) as count_users

from tb3

group by 1