kiacryptodaily bot rate - op
Updated 2023-03-10Copy Reference Fork
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
›
⌄
with opti_bots as (
select
from_address as addr,
date_trunc('second', block_timestamp) as sec,
count(distinct tx_hash) as tx_count
from optimism.core.fact_transactions
group by 1, 2 having tx_count > 10
),
opti_tx as (
select
date_trunc('day', block_timestamp) as day,
count(distinct tx_hash) as tx_count
from optimism.core.fact_transactions
where day >= '2023-01-01'
group by 1
),
info as (
select
date_trunc('day', block_timestamp) as date,
(count(distinct tx_hash) / avg(tx_count)) * 100 as tx_by_bot_rate
from optimism.core.fact_transactions join opti_tx on block_timestamp::date = day
where
date >= '2023-01-01' and from_address in (select addr from opti_bots)
group by 1
),
avg_info as (
select avg(tx_by_bot_rate) as avg_tx_by_bot_rate
from info
)
select *
from info, avg_info
Run a query to Download Data