elsinaDistribution average gap between two transaction of All Blockchain
Updated 2022-12-19
999
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
with op_user_address_with_diff_time as (
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 optimism.core.fact_transactions
), user_address_with_diff_time_with_remove_first_tx as (
select * from op_user_address_with_diff_time where BLOCK_TIMESTAMP!=previous_tx_timestamp
),op_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 op_user_address_with_diff_time
group by FROM_ADDRESS
), eth_user_address_with_diff_time as (
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
), eth_user_address_with_diff_time_with_remove_first_tx as (
select * from eth_user_address_with_diff_time where BLOCK_TIMESTAMP!=previous_tx_timestamp
),eth_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 eth_user_address_with_diff_time
group by FROM_ADDRESS
Run a query to Download Data