WITH tornado_wallets AS ( SELECT address, address_name FROM flipside_prod_db.crosschain.address_labels WHERE address_name ilike '%tornado%' ), in_transfers AS ( SELECT to_address AS wallet , block_timestamp::DATE AS date , contract_address AS token_address , amount FROM ethereum.core.ez_token_transfers WHERE to_address in (SELECT address FROM tornado_wallets) UNION ALL SELECT eth_to_address AS wallet , block_timestamp::DATE AS date , '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' AS token_address , amount FROM ethereum.core.ez_eth_transfers WHERE eth_to_address in (SELECT address FROM tornado_wallets) ), in_amount AS ( SELECT wallet , amount * (SELECT AVG(price) FROM ethereum.core.fact_hourly_token_prices p WHERE i.token_address = p.token_address AND i.date = p.hour::DATE) AS amount_usd , CASE WHEN amount_usd < 100 THEN '< $100' WHEN amount_usd >= 100 AND amount_usd < 1000 THEN '$100 - $1,000' WHEN amount_usd >= 1000 AND amount_usd < 10000 THEN '$1,000 - $10,000' WHEN amount_usd >= 10000 AND amount_usd < 100000 THEN '$10,000 - $100,000' WHEN amount_usd >= 100000 AND amount_usd < 1000000 THEN '$100,000 - $1,000,000' WHEN amount_usd >= 1000000 THEN '> $1,000,000' END AS bin_amount , CASE WHEN amount_usd < 100 THEN 1 WHEN amount_usd >= 100 AND amount_usd < 1000 THEN 2 WHEN amount_usd >= 1000 AND amount_usd < 10000 THEN 3 WHEN amount_usd >= 10000 AND amount_usd < 100000 THEN 4 WHEN amount_usd >= 100000 AND amount_usd < 1000000 THEN 5 WHEN amount_usd >= 1000000 THEN 6 END AS bin_order FROM in_transfers i WHERE amount IS NOT NULL ) SELECT bin_amount , bin_order , SUM(amount_usd) AS usd_amount , COUNT(*) AS tx FROM in_amount JOIN tornado_wallets ON wallet = address GROUP BY 1, 2 HAVING usd_amount IS NOT NULL

Tornado Cash Addresses