THE ON-CHAIN ANALYTICS
EDGE.

-- 3-way self-join: front + back legs (same bot, -- reversed direction) with a victim wedged between WITH dt AS ( SELECT block_number, project_contract_address, tx_from, tx_hash, evt_index, token_bought_address, token_sold_address, amount_usd FROM dex.trades WHERE blockchain='ethereum' AND block_number BETWEEN 18000000 AND 18000100), legs AS ( SELECT f.tx_from AS attacker, f.evt_index fi, b.evt_index bi, f.token_bought_address fb, f.token_sold_address fs FROM dt f JOIN dt b ON f.block_number=b.block_number AND f.project_contract_address=b.project_contract_address AND f.tx_from=b.tx_from AND f.tx_hash<>b.tx_hash -- back leg reverses the front (ADDRESS-level, not symbol) AND b.token_bought_address=f.token_sold_address AND b.token_sold_address=f.token_bought_address AND b.evt_index>f.evt_index+1), -- room for a victim v AS ( SELECT DISTINCT l.attacker, val.tx_from victim, val.amount_usd FROM legs l JOIN dt val ON val.token_bought_address=l.fb AND val.token_sold_address=l.fs AND val.evt_index>l.fi AND val.evt_index<l.bi) SELECT lower(to_hex(attacker)) AS bot, count(*) AS victims, round(sum(amount_usd),0) AS victim_usd FROM v GROUP BY 1 ORDER BY victims DESC LIMIT 3