SELECT from_address, to_address, block_timestamp, tx_hash

FROM ethereum.core.fact_token_transfers

WHERE contract_address = lower('0xdac17f958d2ee523a2206206994597c13d831ec7')

AND raw_amount = 45360000

),

scam_transfer as (

SELECT from_address, to_address, block_timestamp, tx_hash

FROM ethereum.core.fact_token_transfers

WHERE contract_address = lower('0xdac17f958d2ee523a2206206994597c13d831ec7')

AND raw_amount >= 50000000000

AND raw_amount <= 60000000000

-- AND LEFT(lower(to_address), 5) = '0xf14'

SELECT

CASE WHEN s.from_address = '0x56eddb7aa87536c09ccc2793473599fd21a8b17f' THEN 'Binance 🟨'

ELSE 'DEA 🚨'

END AS "Wallet owner",

s.from_address

FROM test_transfer t

JOIN scam_transfer s ON t.to_address != s.to_address

AND LEFT(lower(t.to_address), 5) = LEFT(lower(s.to_address), 5)

AND RIGHT(lower(t.to_address), 3) = RIGHT(lower(s.to_address), 3)

AND date_trunc('day', t.block_timestamp) = date_trunc('day', s.block_timestamp)

AND t.block_timestamp < s.block_timestamp