distinct from_address as "Scammer"

from ethereum.core.ez_token_transfers

where amount_usd = 0

and block_timestamp >= '2022-11-27'

and substr(from_address,0,5) = substr(to_address,0,5)

and substr(from_address,-5,5) = substr(to_address,-5,5)

and from_address != to_address

and from_address not in (select address from ethereum.core.dim_labels)

group by 1),

cte2 as ( --filter all zero tokens transfers from possible poison wallets

select distinct from_address,

to_address

from ethereum.core.ez_token_transfers

where amount_usd = 0

and from_address in (select "Scammer" from cte1)

and block_timestamp >= '2022-11-27'

and from_address != to_address

and from_address not in (select address from ethereum.core.dim_labels)

group by 1,2

),

cte3 as ( -- filter traders if they made a token zero transaction to someone but then sent tokens to them

select distinct from_address,

to_address

from ethereum.core.ez_token_transfers

where amount_usd > 0

and from_address in (select "Scammer" from cte1)

and block_timestamp >= '2022-11-27'

and to_address in (select to_address from cte2)

and from_address != to_address

and from_address not in (select address from ethereum.core.dim_labels)

group by 1,2