SELECT

LOWER(SPLIT(logs.topics, ',')[1]) AS victim_address,

LOWER(SPLIT(logs.topics, ',')[2]) AS attacker_address,

MIN(logs.block_number) AS first_abnormal_block_number

FROM bsc.core.fact_event_logs logs

WHERE logs.block_number > 21800000

AND SPLIT(logs.topics, ',')[0] = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef' -- Transfer event

AND logs.data = '0x0000000000000000000000000000000000000000000000000000000000000000' -- Нулевой перевод

AND logs.contract_address IN (

'0x55d398326f99059fF775485246999027B3197955', -- USDT

'0xe9e7CEA3DedcA5984780Bafc599bD69ADd087D56', -- BUSD

'0x8AC76a51cc950d9822D68b83fE1Ad97B32Cd580d', -- USDC

'0x2170Ed0880ac9A755fd29B2688956BD959F933F8' -- ETH

)

GROUP BY LOWER(SPLIT(logs.topics, ',')[1]), LOWER(SPLIT(logs.topics, ',')[2])

HAVING COUNT(DISTINCT logs.tx_hash) > 10

),

suspious_address_pair_step2 AS (

SELECT

LOWER(SPLIT(t1.topics, ',')[1]) AS victim_address,

LOWER(SPLIT(t1.topics, ',')[2]) AS attacker_address,

t2.first_abnormal_block_number,

MIN(t1.block_number) AS first_normal_block_number

FROM bsc.core.fact_event_logs t1

JOIN suspious_address_pair_step1 t2

ON LOWER(SPLIT(t1.topics, ',')[1]) = t2.victim_address

AND LOWER(SPLIT(t1.topics, ',')[2]) = t2.attacker_address

WHERE t1.data != '0x0000000000000000000000000000000000000000000000000000000000000000'

GROUP BY LOWER(SPLIT(t1.topics, ',')[1]), LOWER(SPLIT(t1.topics, ',')[2]), t2.first_abnormal_block_number

HAVING t2.first_abnormal_block_number < MIN(t1.block_number)

SELECT

LOWER(SPLIT(t1.topics, ',')[1]) AS victim_address,