select proposer,

tx_id,

block_timestamp,

row_number () over (partition by proposer order by block_timestamp) as RN

from flow.core.fact_transactions

where block_timestamp >= '2022-06-01'),

ft2 as (

select proposer,

tx_id,

block_timestamp,

row_number () over (partition by proposer order by block_timestamp) as RN

from flow.core.fact_transactions

where block_timestamp >= '2022-06-01'),

ft3 as (

select t1.proposer,

avg (timediff (hour,t1.block_timestamp,t2.block_timestamp)) as Time_Difference

from ft1 t1 join ft2 t2 on t1.proposer = t2.proposer and t2.rn = t1.rn + 1

group by 1)

select case when Time_Difference < 1 then 'Less Than 1 Hour'

when Time_Difference >= 1 and Time_Difference < 12 then 'Between 1 - 12 Hours'

when Time_Difference >= 12 and Time_Difference < 24 then 'Between 12 - 24 Hours'

when Time_Difference >= 24 and Time_Difference < 48 then 'Between 1 - 2 Days'

when Time_Difference >= 48 and Time_Difference < 168 then 'Between 2 - 7 Days'

when Time_Difference >= 168 and Time_Difference < 336 then 'Between 1 Week - 2 Weeks'

when Time_Difference >= 336 and Time_Difference < 720 then 'Between 2 Weeks - 1 Month'

when Time_Difference >= 720 and Time_Difference < 2160 then 'Between 1 - 3 Months'

when Time_Difference >= 2160 then 'More Than 3 Months' end as Time_Difference1,

count (*)

from ft3

group by 1

order by 2 desc