block_timestamp,

PURCHASER,

tx_id,

row_number () over (partition by PURCHASER order by block_timestamp) as rank

from terra.core.fact_nft_sales

where block_timestamp >= '2022-01-01'

), table2 as (select

block_timestamp,

PURCHASER,

tx_id,

row_number () over (partition by PURCHASER order by block_timestamp) as rank

from terra.core.fact_nft_sales

where block_timestamp >= '2022-01-01'

), main as(select

a.PURCHASER,

avg(timediff (hour,a.block_timestamp,b.block_timestamp)) as "Time difference"

from table1 a join table2 b on a.PURCHASER = b.PURCHASER and a.rank + 1 = b.rank

group by 1

)

when "Time difference" < 1 then 'Less than 1 hour'

when "Time difference" between 1 and 23.99 then 'Between 1 hours till 1 days'

when "Time difference" between 24 and 71.99 then 'Between 1 till 3 days'

when "Time difference" between 72 and 167.99 then 'Between 3 days till 1 weeks'

when "Time difference" between 168 and 335.99 then 'Between 1 till 2 weeks'

when "Time difference" between 336 and 719.99 then 'Between 2 weeks till 1 months'

when "Time difference" between 720 and 2159.99 then 'Between 1 till 3 months'

when "Time difference" between 2160 and 4319.99 then 'Between 3 till 6 months'

when "Time difference" between 4320 and 8759.99 then 'Between 6 month till 1 year'

else 'More than 1 year'

end as "Time difference ",

count (*)

from main

group by 1