mohammadhFlow vs Other L1s
Updated 2022-07-19
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
›
⌄
with A as (select proposer,
Min(date_trunc('day', block_timestamp)) as min_date,
Max(date_trunc('day', block_timestamp)) as max_date,
count(distinct TX_ID) as tx_count
from flow.core.fact_transactions
group by proposer
having Min(date_trunc('day', block_timestamp)) > '2022-4-20'),
B as (select proposer,
avg(datediff('day', min_date, max_date))/avg(tx_count) as Time_Between_Transactions
from A
group by proposer)
select
Case
when TIME_BETWEEN_TRANSACTIONS < 0.25 then 'Less Than 6 hours'
when TIME_BETWEEN_TRANSACTIONS between 0.25 and 0.5 then 'Between 6 and 12 hours'
when TIME_BETWEEN_TRANSACTIONS between 0.5 and 1 then 'Between 12 and 24 hours'
when TIME_BETWEEN_TRANSACTIONS between 1 and 3 then 'Between 1 and 3 days'
when TIME_BETWEEN_TRANSACTIONS between 3 and 7 then 'Between 3 and 7 days'
when TIME_BETWEEN_TRANSACTIONS between 7 and 14 then 'Between 1 and 2 weeks'
when TIME_BETWEEN_TRANSACTIONS between 14 and 30 then 'Between 2 and 4 weeks'
when TIME_BETWEEN_TRANSACTIONS > 30 then 'Greater 1 months'
END as Time_category,
count(proposer)
from B
group by Time_category