s-kTerra Daily Bot Success and Fail Count
Updated 2022-05-03
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
34
35
36
›
⌄
-- Compare the success rate of transactions executed by bots in the past 60 days
-- with average users' transactions in the same time period. Is there any difference and any trends identifiable?
with bot_address as (
select distinct address as address from(
select date_trunc(day, block_timestamp) as date,
tx_from[0] as address,
count(tx_id) as tx_count
from terra.transactions
where block_timestamp::date > dateadd(day, -61, current_date()) and block_timestamp::date <= dateadd(day, -1, current_date())
group by date, address
having tx_count > 100
)
), normal_address as (
select distinct address as address from(
select date_trunc(day, block_timestamp) as date,
tx_from[0] as address,
count(tx_id) as tx_count
from terra.transactions
where block_timestamp::date > dateadd(day, -61, current_date()) and block_timestamp::date <= dateadd(day, -1, current_date())
group by date, address
having tx_count <= 100
)
)
select date_trunc(day, block_timestamp) as date,
count(tx_id) as tx_count,
case when tx_status = 'SUCCEEDED' then 'Success'
else 'Fail' end as type
-- count_if(tx_status = 'SUCCEEDED') as succeeded,
-- count_if(tx_status != 'SUCCEEDED') as failed
from terra.transactions
where block_timestamp::date > dateadd(day, -61, current_date()) and block_timestamp::date <= dateadd(day, -1, current_date())
and (tx_from[0] in (select address from bot_address))
group by date, type
Run a query to Download Data