select

FROM_ADDRESS,

BLOCK_TIMESTAMP,

LAG(BLOCK_TIMESTAMP,1,BLOCK_TIMESTAMP) OVER (

PARTITION BY FROM_ADDRESS

ORDER BY BLOCK_TIMESTAMP

) previous_tx_timestamp,

DATEDIFF(hour, previous_tx_timestamp,BLOCK_TIMESTAMP) as "time diff between two TX"

from avalanche.core.fact_transactions

), avalanche_user_address_with_diff_time_with_remove_first_tx as (

select * from avalanche_user_address_with_diff_time where BLOCK_TIMESTAMP!=previous_tx_timestamp

),avalanche_avrage_time_between_two_tx_for_each_user as (

select

FROM_ADDRESS,

round(avg("time diff between two TX"),0) as "avrage time betwwen two TX"

from avalanche_user_address_with_diff_time

group by FROM_ADDRESS

)

select

case

when "avrage time betwwen two TX" <= 1 then 'Less than 1 Hour'

when "avrage time betwwen two TX" <= 12 then 'between 1 hour and half day'

when "avrage time betwwen two TX" <= 24 then 'between half day and one day'

when "avrage time betwwen two TX" <= 24*7 then 'between one day and one week'

else 'more than one week' end as "Duration"

,count(*) as "repeation"

from avalanche_avrage_time_between_two_tx_for_each_user

group by 1