Aryancombination between real users and bots
Updated 2022-04-28
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
›
⌄
with bots as (
select date_trunc('minute',block_timestamp) as minutes, trader, count(*) as tx_count
from terra.swaps
where block_timestamp::date >= current_date - 60
group by minutes, trader
having tx_count > 25
),
bot_tx as (
select block_timestamp::date as date,
count(case when tx_status = 'SUCCEEDED' then 1 else null end) as success,
count(case when tx_status = 'FAILED' then 1 else null end) as fail
from terra.msgs
where block_timestamp::date > current_date - 60
and msg_value:trader in (select distinct trader from bots)
group by date
),
user_tx as (
select block_timestamp::date as date,
count(case when tx_status = 'SUCCEEDED' then 1 else null end) as success,
count(case when tx_status = 'FAILED' then 1 else null end) as fail
from terra.msgs
where block_timestamp::date > current_date - 60
and msg_value:trader not in (select distinct trader from bots)
group by date
)
select bot_tx.date as exact_date,bot_tx.success as bots_success, bot_tx.fail as bots_failed,
((bots_success / (bots_success + bots_failed)) * 100) as bots_success_percentage,
user_tx.success as users_success, user_tx.fail as users_failed,
((users_success / (users_success + users_failed)) * 100) as users_success_percentage
from bot_tx
join user_tx
where bot_tx.date = user_tx.date
order by exact_date
Run a query to Download Data