KaskoazulPolygon Bots
    Updated 2023-01-18

    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