select *,

row_number () over (partition by buyer order by block_timestamp) as row_no

from flow.core.ez_nft_sales

),

nft_purchases2 as (

select *,

row_number () over (partition by buyer order by block_timestamp) as row_no

from flow.core.ez_nft_sales

),

nft_purchases3 as (

select nft1.buyer as user,

avg (timediff (hour, nft1.block_timestamp, nft2.block_timestamp)) as frequency

from nft_purchases1 nft1 join nft_purchases2 nft2

on (nft1.buyer = nft2.buyer and nft2.row_no = nft1.row_no + 1)

group by user)

select case

when frequency < 24 then '1 day'

when frequency >= 24 and frequency < 168 then '2 to 7 days'

when frequency >= 168 and frequency < 336 then '7 to 14 days'

when frequency >= 336 and frequency < 720 then '14 to 30 days'

when frequency >= 720 and frequency < 1440 then '14 to 30 days'

when frequency >= 1440 and frequency < 2160 then '30 to 90 days'

when frequency >= 2160 and frequency < 2880 then '90 to 120 days'

when frequency >= 2880 and frequency < 3600 then '120 to 150 days'

when frequency >= 3600 then 'beyond 150 days' end as frequency,

count (*) as no_of_users

from nft_purchases3

group by 1

order by 2 desc