KaskoazulPolygon Bots
Updated 2023-01-18
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
›
⌄
WITH txs_by_address AS (
SELECT from_address AS address
,DATE_TRUNC('MINUTE',block_timestamp) AS minuto
,DATE_TRUNC('HOUR',block_timestamp) AS hora
,COUNT(1) AS n_txs
FROM polygon.core.fact_transactions
WHERE block_timestamp BETWEEN '2022-01-01' AND '2022-12-31'
GROUP BY 1,2,3
),
txs_by_hour AS (
-- 3+ hours of 240+ txs
SELECT address
FROM txs_by_address
WHERE n_txs > 240
GROUP BY 1
HAVING COUNT(1) > 3
),
txs_by_minute AS (
-- 3+ minutes of 40+ tx
SELECT address
FROM txs_by_address
WHERE n_txs > 40
GROUP BY 1
HAVING COUNT(1) > 3
)
SELECT
CASE
WHEN COALESCE(h.address, m.address) IS NULL THEN 'HUMAN'
ELSE 'BOT'
END AS user_type
, COUNT(DISTINCT from_address) AS n_addresses
, ROUND(AVG(CASE WHEN status = 'SUCCESS' THEN 0 ELSE 1 END)*100,2) AS pct_tx_fail
, COUNT(1) AS txs