s-kTotal Bot Transactions
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 count(distinct tx_from[0]), 'bot' as address
, count(tx_id) as total_tx 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))
UNION
select count(distinct tx_from[0]), 'normal'
, count(tx_id) as total_tx 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 normal_address))
Run a query to Download Data