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 Ethereum.core.fact_transactions

), user_address_with_diff_time_with_remove_first_tx as (

select * from user_address_with_diff_time where BLOCK_TIMESTAMP!=previous_tx_timestamp

select

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

from user_address_with_diff_time