MLDZMNaverage time ETH
Updated 2022-06-21
99
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 tb1 as (select
BLOCK_TIMESTAMP,
FROM_ADDRESS,
ROW_NUMBER() OVER (partition by FROM_ADDRESS order by BLOCK_TIMESTAMP) as t_n
from ethereum.core.fact_transactions
where STATUS='SUCCESS'
),
tb2 as (select
BLOCK_TIMESTAMP as first_transaction,
FROM_ADDRESS
from tb1
where t_n=1),
tb3 as (select
BLOCK_TIMESTAMP as second_transaction,
FROM_ADDRESS
from tb1
where FROM_ADDRESS in (select FROM_ADDRESS from tb2)
and t_n=2)
,
tb4 as (select
tb2.FROM_ADDRESS,
avg(DATEDIFF(day,first_transaction, second_transaction )) as time_between
from tb2
join tb3 on tb2.FROM_ADDRESS=tb3.FROM_ADDRESS
group by 1)
select
case when time_between<1 then 'under 1 day'
when time_between>=1 and time_between<7 then 'Under 1 week'
when time_between>=7 and time_between<30 then 'Between 1 week to 1 month'
when time_between>=30 then 'Over 1 month'
end as buckets,
count(distinct FROM_ADDRESS) as count_users
Run a query to Download Data