-- forked from Ario / Tornado Cash Sanctions - Addresses with the Most Number of Transactions since August 1st @ https://flipsidecrypto.xyz/Ario/q/7d7WMxTHdNEQ/tornado-cash-sanctions-addresses-with-the-most-number-of-transactions-since-august-1st
with tornado_addresses as (
select ADDRESS from flipside_prod_db.crosschain.address_labels
where project_name = 'tornado cash'),
ETH_PRICE as (
select
HOUR::date as date,
avg(PRICE) as avg_price
from ethereum.core.fact_hourly_token_prices
where symbol = 'WETH'
group by date
),
tx_to_tornado as (
select
t.block_timestamp,
t.FROM_ADDRESS,
t.ETH_Value * p.avg_price as USD_Value
from ethereum.core.fact_transactions t join ETH_PRICE p on t.block_timestamp::date = p.date
where t.TO_ADDRESS in (select address from tornado_addresses)
and t.STATUS = 'SUCCESS'
and t.block_timestamp >= '2023-06-11'
)
select
FROM_ADDRESS,
Count(*) as number_of_tx
from tx_to_tornado
group by FROM_ADDRESS
order by number_of_tx desc
limit 1000