Avg Time Between TXs | User Count | |
|---|---|---|
1 | 1Day-3Days | 2095459 |
2 | 12Hours-1Day | 1599939 |
3 | <=12 Hours | 1002103 |
4 | 3Days-1Week | 717957 |
5 | 1Week-1Month | 478703 |
6 | >1 Month | 137980 |
Eman-RazDistribution of Users By Average Time between Txns
Updated 2025-03-22
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
›
⌄
with tab1 as (select from_address as user, block_timestamp as txs_date,
lag(block_timestamp) over (partition by user order by block_timestamp) as Previous_transaction_date
from ronin.core.fact_transactions
where tx_succeeded='TRUE'),
tab2 as (select user, avg(datediff(hour,Previous_transaction_date, txs_date)) as avg_time_gap
from tab1
where Previous_transaction_date is not null
group by user
order by user)
select case
when avg_time_gap<=12 then '<=12 Hours'
when avg_time_gap>12 and avg_time_gap<=24 then '12Hours-1Day'
when avg_time_gap>24 and avg_time_gap<=72 then '1Day-3Days'
when avg_time_gap>72 and avg_time_gap<=168 then '3Days-1Week'
when avg_time_gap>168 and avg_time_gap<=720 then '1Week-1Month'
else '>1 Month' end as "Avg Time Between TXs", count(distinct user) as "User Count"
from tab2
group by 1
order by 2 desc
Last run: about 1 month ago
6
139B
9s