with weth_and_torn_hourly_prices as ( select hour , symbol , price from ethereum.core.fact_hourly_token_prices where symbol in ('TORN' , 'WETH') and hour > '2021-08-10' ), tornado_cash_addresses as ( select address , address_name from ethereum.core.dim_labels where label = 'tornado cash' ), tornado_cash_txs as ( select * from (select block_timestamp , tx_hash , origin_to_address , origin_from_address , 'ETH' as symbol , amount , amount_usd from ethereum.core.ez_eth_transfers where origin_to_address in (select address from tornado_cash_addresses) and block_timestamp::date >= '2021-08-10' and block_timestamp::date < '2022-08-10' union select block_timestamp , tx_hash , origin_to_address , origin_from_address , case when symbol = 'vTORN' then 'TORN' else symbol end as symbol , amount , amount_usd from ethereum.core.ez_token_transfers where origin_to_address in (select address from tornado_cash_addresses) and block_timestamp::date >= '2021-08-10' and block_timestamp::date < '2022-08-10' ) ), tornado_cash_txs2 as ( select block_timestamp , tx_hash , origin_to_address , a.symbol , amount , case when amount_usd is null then (amount*price) else amount_usd end as amount_in_usd from tornado_cash_txs a left join weth_and_torn_hourly_prices b on amount_usd is null and a.symbol = b.symbol and trunc(block_timestamp,'hour') = hour ), tornado_cash_transactions_volume as ( select tx_hash , sum(amount_in_usd) as transaction_volume_in_usd from tornado_cash_txs2 group by 1 ), tornado_cash_transactions_volume_with_label as ( select tx_hash , case when (transaction_volume_in_usd < 1) then 'TRANSACTION VOLUME < $1' when (transaction_volume_in_usd >= 1 and transaction_volume_in_usd < 10) then '$1 ≤ TRANSACTION VOLUME < &10' when (transaction_volume_in_usd >= 10 and transaction_volume_in_usd < 100) then '$10 ≤ TRANSACTION VOLUME < &100' when (transaction_volume_in_usd >= 100 and transaction_volume_in_usd < 1000) then '$100 ≤ TRANSACTION VOLUME < &1000' when (transaction_volume_in_usd >= 1000 and transaction_volume_in_usd < 10000) then '$1,000 ≤ TRANSACTION VOLUME < &10,000' when (transaction_volume_in_usd >= 10000 and transaction_volume_in_usd < 100000) then '$10,000 ≤ TRANSACTION VOLUME < &100,000' when (transaction_volume_in_usd >= 100000 and transaction_volume_in_usd < 1000000) then '$100,000 ≤ TRANSACTION VOLUME < &1,000,000' when (transaction_volume_in_usd >= 1000000 ) then '$1,000,000 ≤ TRANSACTION VOLUME' end as tx_volume_label from tornado_cash_transactions_volume ) select tx_volume_label as "TRANSACTION VOLUME GROUP" , count(tx_hash) as "NUMBER OF TRANSACTIONS" from tornado_cash_transactions_volume_with_label group by 1 order by 2 desc

cancelo-sergio