elsinahome: Transactions bot rate
Updated 2022-08-27
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
›
⌄
with bots as (
select date_trunc('minute', block_timestamp) as min, from_address as addr, count(*) as tx_count
from optimism.core.fact_transactions
where block_timestamp::date <= current_date - 1
group by 1, 2 having tx_count >= 10
),
bot_count as (
select block_timestamp::date as date, count(distinct from_address) as bot_count
from avalanche.core.fact_transactions
where date <= current_date - 1 and from_address in (select addr from bots)
group by 1
),
all_count as (
select block_timestamp::date as date, count(distinct from_address) as all_count
from avalanche.core.fact_transactions
where date <= current_date - 1
group by 1
)
select b.date as "Day", (bot_count/all_count) * 100 as "Bot Rate"
from bot_count b, all_count a
where b.date = a.date
Run a query to Download Data