select

proposer,

Min(date_trunc('hour', block_timestamp)) as min_date,

Max(date_trunc('hour', block_timestamp)) as max_date,

count(*) as transactions

from flow.core.fact_transactions

group by 1

having Min(date_trunc('hour', block_timestamp)) < current_date - 5

)

,table2 as (

select

proposer,

avg(datediff('hour', min_date, max_date))/avg(transactions) as Time_Between_Transactions

from table1

group by 1

)

select

Case

when TIME_BETWEEN_TRANSACTIONS < 1 then 'Less Than an Hour'

when TIME_BETWEEN_TRANSACTIONS between 1 and 10 then 'Between 1 and 10 Hours'

when TIME_BETWEEN_TRANSACTIONS between 10 and 20 then 'Between 10 and 20 Hours'

when TIME_BETWEEN_TRANSACTIONS between 20 and 40 then 'Between 20 and 40 Hours'

when TIME_BETWEEN_TRANSACTIONS between 40 and 80 then 'Between 40 and 80 Hours'

when TIME_BETWEEN_TRANSACTIONS > 80 then 'Greater than 80 Hours'

END as time_between,

count(*)

from table2

group by 1