SELECT *

FROM ethereum.core.dim_labels

WHERE label = 'tornado cash'

), tc_txs AS (

SELECT a.tx_hash, a.eth_value as amount_eth, a.to_address, b.address_name

FROM ethereum.core.fact_transactions AS a JOIN tc_addresses as b on a.TO_ADDRESS = b.address

WHERE a.status = 'SUCCESS' AND a.eth_value > 0 AND a.block_timestamp > CURRENT_DATE - 30

), tc_token_tx AS (

SELECT a.*, b.*, date_trunc(HOUR, a.block_timestamp) as tx_hour

FROM ethereum.core.ez_token_transfers as a JOIN tc_addresses as b on a.origin_to_address = b.address

), tc_token_with_price AS (

SELECT a.tx_hash, a.amount_usd/b.eth_price as amount_eth, a.origin_to_address as to_address, a.address_name

FROM tc_token_tx as a JOIN (

SELECT hour, avg(price) as eth_price

FROM ethereum.core.fact_hourly_token_prices

WHERE token_address is NULL and hour > CURRENT_DATE - 30

GROUP BY hour

) as b ON a.tx_hour = b.hour

), all_txs AS (

SELECT * FROM tc_txs

UNION

SELECT * FROM tc_token_with_price

), tx_final AS (

SELECT *,

CASE

WHEN amount_eth < 0.01 THEN 'Less than 0.01 ETH'

WHEN amount_eth BETWEEN 0.01 AND 0.1 THEN '0.01 - 0.1 ETH'

WHEN amount_eth BETWEEN 0.1 AND 1 THEN '0.1 - 1 ETH'

WHEN amount_eth BETWEEN 1 AND 10 THEN '1 - 10 ETH'

WHEN amount_eth BETWEEN 10 AND 100 THEN '10 -100 ETH'

ELSE 'more than 100 ETH'

END AS value_grp

FROM all_txs

SELECT to_address, address_name, sum(amount_eth)