HadisehBots Successful Vs. Fail swap
Updated 2022-05-05
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
›
⌄
with swaps as ( select trunc(BLOCK_TIMESTAMP,'minute') as minutely , trader , TX_STATUS, count(tx_id) as total_bot_swaps
from terra.swaps
where block_timestamp::date >= CURRENT_DATE - 60
group by 1,2,3
having total_bot_swaps >= 20
order by 1)
,
user_status as (select trunc(block_timestamp,'day') as user_date,
count(case when tx_status = 'SUCCEEDED' then tx_status end) as users_success, count(case when tx_status = 'FAILED' then tx_status end) as user_fail
from terra.msgs
where user_date >= current_date - 60 and msg_value:trader not in (select distinct trader from swaps)
group by 1
order by 1
),
bot_status as (
select trunc(block_timestamp,'day') as bot_date,
count(case when tx_status = 'SUCCEEDED' then tx_status end) as bots_success, count(case when tx_status = 'FAILED' then tx_status end) as bot_fail
from terra.msgs
where bot_date >= current_date - 60 and msg_value:trader in (select distinct trader from swaps)
group by 1
)
select 'Success_bot' ,sum(bots_success) as bots_success
from bot_status
group by 1
UNION
select 'Fail_bot', sum(bot_fail) as bot_fail
from bot_status
group by 1
Run a query to Download Data