distinct BUYER as u1,

y.PLAY_TYPE,

min(BLOCK_TIMESTAMP) as x1

from flow.core.fact_nft_sales x left outer join flow.core.dim_topshot_metadata y on x.NFT_ID=y.NFT_ID

where TX_SUCCEEDED='TRUE'

and x.NFT_COLLECTION='A.0b2a3299cc857e29.TopShot'

and x.CURRENCY='A.ead892083b3e2c6c.DapperUtilityCoin'

and BLOCK_TIMESTAMP>=CURRENT_DATE-90

group by 1,2 having PLAY_TYPE is not null ),

tb2 as (SELECT

distinct SELLER as u2,

y.PLAY_TYPE,

min(BLOCK_TIMESTAMP) as x2

from flow.core.fact_nft_sales x left outer join flow.core.dim_topshot_metadata y on x.NFT_ID=y.NFT_ID

where TX_SUCCEEDED='TRUE'

and x.NFT_COLLECTION='A.0b2a3299cc857e29.TopShot'

and x.CURRENCY='A.ead892083b3e2c6c.DapperUtilityCoin'

and BLOCK_TIMESTAMP>=CURRENT_DATE-90

and SELLER in (select u1 from tb1)

group by 1,2 having PLAY_TYPE is not null) ,

tb3 as (select

tb1.u1 as purchaser,

tb1.PLAY_TYPE,

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

from tb1

join tb2 on tb1.u1=tb2.u2

group by 1,2)

PLAY_TYPE,

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'