select

from_address,

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

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

count(*) as transactions

from ethereum.core.fact_transactions

group by 1

having Min(date_trunc('day', block_timestamp)) < current_date - 2

)

, tab2 as (

select

from_address,

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

from tab1

group by 1

)

select

Case

when TIME_BETWEEN_TRANSACTIONS < 1 then 'Less Than an Day'

when TIME_BETWEEN_TRANSACTIONS between 1 and 2 then 'Between 1 and 2 Days'

when TIME_BETWEEN_TRANSACTIONS between 2 and 5 then 'Between 2 and 5 Days'

when TIME_BETWEEN_TRANSACTIONS between 5 and 10 then 'Between 5 and 10 Days'

when TIME_BETWEEN_TRANSACTIONS between 10 and 15 then 'Between 10 and 15 Days'

when TIME_BETWEEN_TRANSACTIONS between 15 and 30 then 'Between 15 and 30 Days'

when TIME_BETWEEN_TRANSACTIONS between 30 and 60 then 'Between 30 and 60 Days'

when TIME_BETWEEN_TRANSACTIONS > 60 then 'Greater than 60 Days'

END as time_between,

count(*)

from tab2

group by 1