with tornado_addresses as ( select initcap(substr(address_name, position(':', address_name)+1)) as address_name , address -- , * from flipside_prod_db.crosschain.address_labels where project_name = 'tornado cash' ) , from_address_tx as ( select distinct tx_hash , origin_from_address from ethereum.core.fact_event_logs where (origin_to_address in (select address from tornado_addresses) or contract_address in (select address from tornado_addresses)) and origin_from_address not in (select address from tornado_addresses) and block_timestamp >= CURRENT_DATE - interval'1 years' ) , eth_transfers as ( SELECT distinct block_timestamp , 'ETH' as symbol , tx_hash , first_value(amount) over (partition by tx_hash order by amount desc ) as amount , first_value(amount_usd) over (partition by tx_hash order by amount_usd desc ) as amount_usd , 'ETH Token' as type FROM ethereum.core.ez_eth_transfers where tx_hash in (select tx_hash from from_address_tx) -- and origin_from_address in (select origin_from_address from from_address_tx ) -- limit 10 ), distinct_tokens_transfers as ( SELECT distinct symbol FROM ethereum.core.ez_token_transfers where tx_hash in (select tx_hash from from_address_tx) and symbol is not null ) , daily_prices as ( select distinct hour::date as day , symbol , avg(price) over(partition by day,symbol) as price from ethereum.core.fact_hourly_token_prices where lower(symbol) in (select lower(symbol) from distinct_tokens_transfers) and hour >= CURRENT_DATE - INTERVAL'1 years' ) , token_transfers as ( SELECT distinct tr.block_timestamp , hp.symbol , tx_hash , first_value(amount) over (partition by tx_hash order by amount desc ) as amount , first_value(price*amount) over (partition by tx_hash order by amount desc ) as amount_usd , 'Non-ETH Tokens' as type -- , * FROM -- ethereum.core.ez_token_transfers tr left outer join hourly_prices hp on date_trunc('hour',tr.block_timestamp) = hp.hour and hp.symbol=tr.symbol ethereum.core.ez_token_transfers tr inner join daily_prices hp on tr.block_timestamp::date= hp.day and lower(hp.symbol)=lower(tr.symbol) where tx_hash in (select tx_hash from from_address_tx) and tr.symbol not in ('META', 'COLLAR') ) , all_transfers as ( select distinct tx_hash, block_timestamp::date as day, tr.symbol, amount_usd, type , price from token_transfers tr inner join daily_prices hp on tr.block_timestamp::date= hp.day and lower(hp.symbol)=lower(tr.symbol) UNION select distinct tx_hash, block_timestamp::date as day, tr.symbol, amount_usd , type , price from eth_transfers tr inner join daily_prices hp on tr.block_timestamp::date= hp.day and lower(hp.symbol)=lower(tr.symbol) ) , all_eth_extracted as ( SELECT tx_hash , tr.DAY , tr.symbol , amount_usd , hp.price as eth_price , tr.amount_usd / hp.price as eth_equivalent , hp.symbol as ETH_Symbol -- distinct date_trunc('week', block_timestamp) as week -- , type -- ,sum(amount_usd) over(partition by week, type) as daily_transfer_volume_in_usd_to_tornado_cash FROM all_transfers tr inner join daily_prices hp on tr.day= hp.day where hp.symbol= 'WETH' -- LIMIT 10 ) select distinct case when eth_equivalent <= 0.1 then '<= 0.1 ETH' when eth_equivalent <= 1 then '0.1 < ETH <=1' when eth_equivalent <= 10 then '1 < ETH <=10' when eth_equivalent <= 100 then '10 < ETH <=100' else '> 100 ETH' end as eth_bucket , count(distinct tx_hash) over(partition by eth_bucket) as bucket_stats from all_eth_extracted

Tornado Cash Banned Addresses Analysis