KaskoazulSolana Bots
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 t0 AS (
SELECT signers[0]::string AS address
, DATE_TRUNC('minute', block_timestamp) AS minute
, DATE_TRUNC('hour', block_timestamp) AS hour
, COUNT(1) AS n_tx
FROM solana.core.fact_transactions
WHERE block_timestamp >= '2020-09-01'
GROUP BY 1, 2, 3
), t1 AS (
-- 3+ minutes of 40+ tx
SELECT address
FROM t0
WHERE n_tx > 40
GROUP BY 1
HAVING COUNT(1) >= 3
), t2 AS (
-- 3+ hours of 240+ tx
SELECT address
, hour
, SUM(n_tx) AS mx_tx
FROM t0
GROUP BY 1, 2
HAVING SUM(n_tx) > 240
), t3 AS (
-- 3+ days of 240+ tx
SELECT address
FROM t2
GROUP BY 1
HAVING COUNT(1) >= 3
)
SELECT CASE WHEN COALESCE(t1.address, t3.address) IS NULL THEN 'A: Human' ELSE 'B: Bot' END AS user_type
, COUNT(DISTINCT t.signers[0]::string) AS n_addresses
, 100 * AVG(CASE WHEN succeeded THEN 0 ELSE 1 END) AS pct_tx_fail
, COUNT(1) AS n_tx
, SUM(CASE WHEN succeeded THEN 0 ELSE 1 END) AS n_tx_fail
Run a query to Download Data