select

buyer,

datediff('day', lag(block_timestamp) over (partition by buyer order by block_timestamp), block_timestamp) as purchaseinterval

from

flow.nft.ez_nft_sales

where nft_collection = 'A.e4cf4bdc1751c65d.AllDay'

and tx_succeeded = true

case

when purchaseinterval is null then 'FIRST PURCHASE'

when purchaseinterval <= 7 then '1 WEEK OR LESS'

when purchaseinterval <= 30 then '1-30 DAYS'

when purchaseinterval <= 90 then '31-90 DAYS'

when purchaseinterval <= 180 then '91-180 DAYS'

when purchaseinterval <= 365 then '181-365 DAYS'

else 'MORE THAN 365 DAYS'

end as purchasefrequencybucket,

count(distinct buyer) as usersinbucket

userpurchaseintervals

group by

purchasefrequencybucket

order by

min(purchaseinterval);