with bsctable1 as (

select from_address,

tx_hash,

block_timestamp,

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

from bsc.core.fact_transactions

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

bsctable2 as (

select from_address,

tx_hash,

block_timestamp,

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

from bsc.core.fact_transactions

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

bsctable3 as (

select t1.from_address,

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

from bsctable1 t1 join bsctable2 t2 on t1.from_address = t2.from_address 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 bsctable3

group by 1

order by 2 desc