select from_address as "Scam", to_address as "Affected"

from ethereum.core.ez_token_transfers

where amount_usd < 0.1

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

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)

),

cte2 as ( --Get Scam Wallets winnings in tokens, filter by the initial poisoning trend date

select

to_address as "Poison Wallets",

sum(amount_usd) as "$USD Received"

from ethereum.core.ez_token_transfers

where to_address in (select distinct "Scam" from cte1)

and block_timestamp >= '2022-11-27'

and amount_usd is not null

group by 1

),

cte3 as ( --Get Scam Wallets winnings in ethereum, filter by the initial poisoning trend date

select

eth_to_address as "eth Poison Wallets",

sum(amount_usd) as "eth $USD Received"

from ethereum.core.ez_eth_transfers

where eth_to_address in (select distinct "Scam" from cte1)

and block_timestamp >= '2022-11-27'

and amount_usd is not null

group by 1

select "Poison Wallets",

case when "$USD Received" is null then 0 else "$USD Received" end as "Token $USD",

case when "eth $USD Received" is null then 0 else "eth $USD Received" end as "ETH $USD",

"Token $USD" + "ETH $USD" as "$USD Winnings"

from cte2 left join cte3 on "Poison Wallets" = "eth Poison Wallets"

order by 4 desc