KaskoazulSolana Bots

    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