KaskoazulTerra Failure days bot/ vs human
Updated 2022-03-21
999
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
›
⌄
with USER_TYPE as (
select date_trunc ('month', block_timestamp) as fecha,
count(distinct tx_id) as interactions,
case when interactions >= 300 then 'BOT'
else 'HUMAN' end as type,
tx_from as users
from terra.transactions
where --fecha >= '2021-11-01'
--and fecha < '2022-03-20'
fecha >= '2022-03-01'
group by fecha, users
),
SUCCESS_BOTS as (
select block_timestamp::date as fecha,
count(distinct tx_id) as successful_bots_txs,
ut.type as user
from terra.transactions t
left join USER_TYPE ut
on t.tx_from = ut.users
where fecha >= '2021-11-01'
and fecha < '2022-03-20'
and tx_status = 'SUCCEEDED'
and user = 'BOT'
group by 1,3
),
FAILED_BOTS as (
select block_timestamp::date as fecha,
count(distinct tx_id) as failed_bots_txs,
ut.type as user
from terra.transactions t
left join USER_TYPE ut
on t.tx_from = ut.users
where fecha >= '2021-11-01'
and fecha < '2022-03-20'