WITH TORNADO_CASH_ADDRESSES AS ( SELECT ADDRESS_NAME, ADDRESS FROM flipside_prod_db.crosschain.address_labels WHERE PROJECT_NAME='tornado cash' ), TX_IN_BUCKET1_LAST_MONTH AS ( SELECT '0.01 TO 0.1 ETH' AS BUCKET, 'Last Month' AS DURATION, COUNT(DISTINCT(TX_HASH)) AS TRANS_COUNT FROM ethereum.core.fact_transactions WHERE TO_ADDRESS IN (SELECT ADDRESS FROM TORNADO_CASH_ADDRESSES) AND ETH_VALUE >= 0.01 AND ETH_VALUE <= 0.1 AND BLOCK_TIMESTAMP::DATE > CURRENT_DATE - 30 ), TX_IN_BUCKET1_LAST_QUARTER AS ( SELECT '0.01 TO 0.1 ETH' AS BUCKET, 'Last Quarter' AS DURATION, COUNT(DISTINCT(TX_HASH)) AS TRANS_COUNT FROM ethereum.core.fact_transactions WHERE TO_ADDRESS IN (SELECT ADDRESS FROM TORNADO_CASH_ADDRESSES) AND ETH_VALUE >= 0.01 AND ETH_VALUE <= 0.1 AND BLOCK_TIMESTAMP::DATE > CURRENT_DATE - 90 ), TX_IN_BUCKET1_LAST_YEAR AS ( SELECT '0.01 TO 0.1 ETH' AS BUCKET, 'Last Year' AS DURATION, COUNT(DISTINCT(TX_HASH)) AS TRANS_COUNT FROM ethereum.core.fact_transactions WHERE TO_ADDRESS IN (SELECT ADDRESS FROM TORNADO_CASH_ADDRESSES) AND ETH_VALUE >= 0.01 AND ETH_VALUE <= 0.1 AND BLOCK_TIMESTAMP::DATE > CURRENT_DATE - 365 ), TX_IN_BUCKET1_OVERALL AS ( SELECT '0.01 TO 0.1 ETH' AS BUCKET, 'Over all Time' AS DURATION, COUNT(DISTINCT(TX_HASH)) AS TRANS_COUNT FROM ethereum.core.fact_transactions WHERE TO_ADDRESS IN (SELECT ADDRESS FROM TORNADO_CASH_ADDRESSES) AND ETH_VALUE >= 0.01 AND ETH_VALUE <= 0.1 ), TX_IN_BUCKET2_LAST_MONTH AS ( SELECT '0.1 TO 1 ETH' AS BUCKET, 'Last Month' AS DURATION, COUNT(DISTINCT(TX_HASH)) AS TRANS_COUNT FROM ethereum.core.fact_transactions WHERE TO_ADDRESS IN (SELECT ADDRESS FROM TORNADO_CASH_ADDRESSES) AND ETH_VALUE > 0.1 AND ETH_VALUE <= 1 AND BLOCK_TIMESTAMP::DATE > CURRENT_DATE - 30 ), TX_IN_BUCKET2_LAST_QUARTER AS ( SELECT '0.1 TO 1 ETH' AS BUCKET, 'Last Quarter' AS DURATION, COUNT(DISTINCT(TX_HASH)) AS TRANS_COUNT FROM ethereum.core.fact_transactions WHERE TO_ADDRESS IN (SELECT ADDRESS FROM TORNADO_CASH_ADDRESSES) AND ETH_VALUE > 0.1 AND ETH_VALUE <= 1 AND BLOCK_TIMESTAMP::DATE > CURRENT_DATE - 90 ), TX_IN_BUCKET2_LAST_YEAR AS ( SELECT '0.1 TO 1 ETH' AS BUCKET, 'Last Year' AS DURATION, COUNT(DISTINCT(TX_HASH)) AS TRANS_COUNT FROM ethereum.core.fact_transactions WHERE TO_ADDRESS IN (SELECT ADDRESS FROM TORNADO_CASH_ADDRESSES) AND ETH_VALUE > 0.1 AND ETH_VALUE <= 1 AND BLOCK_TIMESTAMP::DATE > CURRENT_DATE - 365 ), TX_IN_BUCKET2_OVERALL AS ( SELECT '0.1 TO 1 ETH' AS BUCKET, 'Over all Time' AS DURATION, COUNT(DISTINCT(TX_HASH)) AS TRANS_COUNT FROM ethereum.core.fact_transactions WHERE TO_ADDRESS IN (SELECT ADDRESS FROM TORNADO_CASH_ADDRESSES) AND ETH_VALUE > 0.1 AND ETH_VALUE <= 1 ), TX_IN_BUCKET3_LAST_MONTH AS ( SELECT '1 TO 10 ETH' AS BUCKET, 'Last Month' AS DURATION, COUNT(DISTINCT(TX_HASH)) AS TRANS_COUNT FROM ethereum.core.fact_transactions WHERE TO_ADDRESS IN (SELECT ADDRESS FROM TORNADO_CASH_ADDRESSES) AND ETH_VALUE > 1 AND ETH_VALUE <= 10 AND BLOCK_TIMESTAMP::DATE > CURRENT_DATE - 30 ), TX_IN_BUCKET3_LAST_QUARTER AS ( SELECT '1 TO 10 ETH' AS BUCKET, 'Last Quarter' AS DURATION, COUNT(DISTINCT(TX_HASH)) AS TRANS_COUNT FROM ethereum.core.fact_transactions WHERE TO_ADDRESS IN (SELECT ADDRESS FROM TORNADO_CASH_ADDRESSES) AND ETH_VALUE > 1 AND ETH_VALUE <= 10 AND BLOCK_TIMESTAMP::DATE > CURRENT_DATE - 90 ), TX_IN_BUCKET3_LAST_YEAR AS ( SELECT '1 TO 10 ETH' AS BUCKET, 'Last Year' AS DURATION, COUNT(DISTINCT(TX_HASH)) AS TRANS_COUNT FROM ethereum.core.fact_transactions WHERE TO_ADDRESS IN (SELECT ADDRESS FROM TORNADO_CASH_ADDRESSES) AND ETH_VALUE > 1 AND ETH_VALUE <= 10 AND BLOCK_TIMESTAMP::DATE > CURRENT_DATE - 365 ), TX_IN_BUCKET3_OVERALL AS ( SELECT '1 TO 10 ETH' AS BUCKET, 'Over all Time' AS DURATION, COUNT(DISTINCT(TX_HASH)) AS TRANS_COUNT FROM ethereum.core.fact_transactions WHERE TO_ADDRESS IN (SELECT ADDRESS FROM TORNADO_CASH_ADDRESSES) AND ETH_VALUE > 1 AND ETH_VALUE <= 10 ), TX_IN_BUCKET4_LAST_MONTH AS ( SELECT '10 TO 100 ETH' AS BUCKET, 'Last Month' AS DURATION, COUNT(DISTINCT(TX_HASH)) AS TRANS_COUNT FROM ethereum.core.fact_transactions WHERE TO_ADDRESS IN (SELECT ADDRESS FROM TORNADO_CASH_ADDRESSES) AND ETH_VALUE > 10 AND ETH_VALUE <= 100 AND BLOCK_TIMESTAMP::DATE > CURRENT_DATE - 30 ), TX_IN_BUCKET4_LAST_QUARTER AS ( SELECT '10 TO 100 ETH' AS BUCKET, 'Last Quarter' AS DURATION, COUNT(DISTINCT(TX_HASH)) AS TRANS_COUNT FROM ethereum.core.fact_transactions WHERE TO_ADDRESS IN (SELECT ADDRESS FROM TORNADO_CASH_ADDRESSES) AND ETH_VALUE > 10 AND ETH_VALUE <= 100 AND BLOCK_TIMESTAMP::DATE > CURRENT_DATE - 90 ), TX_IN_BUCKET4_LAST_YEAR AS ( SELECT '10 TO 100 ETH' AS BUCKET, 'Last Year' AS DURATION, COUNT(DISTINCT(TX_HASH)) AS TRANS_COUNT FROM ethereum.core.fact_transactions WHERE TO_ADDRESS IN (SELECT ADDRESS FROM TORNADO_CASH_ADDRESSES) AND ETH_VALUE > 10 AND ETH_VALUE <= 100 AND BLOCK_TIMESTAMP::DATE > CURRENT_DATE - 365 ), TX_IN_BUCKET4_OVERALL AS ( SELECT '10 TO 100 ETH' AS BUCKET, 'Over all Time' AS DURATION, COUNT(DISTINCT(TX_HASH)) AS TRANS_COUNT FROM ethereum.core.fact_transactions WHERE TO_ADDRESS IN (SELECT ADDRESS FROM TORNADO_CASH_ADDRESSES) AND ETH_VALUE > 10 AND ETH_VALUE <= 100 ), TX_IN_BUCKET5_LAST_MONTH AS ( SELECT '100 TO 1000 ETH' AS BUCKET, 'Last Month' AS DURATION, COUNT(DISTINCT(TX_HASH)) AS TRANS_COUNT FROM ethereum.core.fact_transactions WHERE TO_ADDRESS IN (SELECT ADDRESS FROM TORNADO_CASH_ADDRESSES) AND ETH_VALUE > 100 AND BLOCK_TIMESTAMP::DATE > CURRENT_DATE - 30 ), TX_IN_BUCKET5_LAST_QUARTER AS ( SELECT '100 TO 1000 ETH' AS BUCKET, 'Last Quarter' AS DURATION, COUNT(DISTINCT(TX_HASH)) AS TRANS_COUNT FROM ethereum.core.fact_transactions WHERE TO_ADDRESS IN (SELECT ADDRESS FROM TORNADO_CASH_ADDRESSES) AND ETH_VALUE > 100 AND BLOCK_TIMESTAMP::DATE > CURRENT_DATE - 90 ), TX_IN_BUCKET5_LAST_YEAR AS ( SELECT '100 TO 1000 ETH' AS BUCKET, 'Last Year' AS DURATION, COUNT(DISTINCT(TX_HASH)) AS TRANS_COUNT FROM ethereum.core.fact_transactions WHERE TO_ADDRESS IN (SELECT ADDRESS FROM TORNADO_CASH_ADDRESSES) AND ETH_VALUE > 100 AND BLOCK_TIMESTAMP::DATE > CURRENT_DATE - 365 ), TX_IN_BUCKET5_OVERALL AS ( SELECT '100 TO 1000 ETH' AS BUCKET, 'Over all Time' AS DURATION, COUNT(DISTINCT(TX_HASH)) AS TRANS_COUNT FROM ethereum.core.fact_transactions WHERE TO_ADDRESS IN (SELECT ADDRESS FROM TORNADO_CASH_ADDRESSES) AND ETH_VALUE > 100 ) SELECT * FROM TX_IN_BUCKET1_LAST_MONTH UNION ALL SELECT * FROM TX_IN_BUCKET1_LAST_QUARTER UNION ALL SELECT * FROM TX_IN_BUCKET1_LAST_YEAR UNION ALL SELECT * FROM TX_IN_BUCKET1_OVERALL UNION ALL SELECT * FROM TX_IN_BUCKET2_LAST_MONTH UNION ALL SELECT * FROM TX_IN_BUCKET2_LAST_QUARTER UNION ALL SELECT * FROM TX_IN_BUCKET2_LAST_YEAR UNION ALL SELECT * FROM TX_IN_BUCKET2_OVERALL UNION ALL SELECT * FROM TX_IN_BUCKET3_LAST_MONTH UNION ALL SELECT * FROM TX_IN_BUCKET3_LAST_QUARTER UNION ALL SELECT * FROM TX_IN_BUCKET3_LAST_YEAR UNION ALL SELECT * FROM TX_IN_BUCKET3_OVERALL UNION ALL SELECT * FROM TX_IN_BUCKET4_LAST_MONTH UNION ALL SELECT * FROM TX_IN_BUCKET4_LAST_QUARTER UNION ALL SELECT * FROM TX_IN_BUCKET4_LAST_YEAR UNION ALL SELECT * FROM TX_IN_BUCKET4_OVERALL UNION ALL SELECT * FROM TX_IN_BUCKET5_LAST_MONTH UNION ALL SELECT * FROM TX_IN_BUCKET5_LAST_QUARTER UNION ALL SELECT * FROM TX_IN_BUCKET5_LAST_YEAR UNION ALL SELECT * FROM TX_IN_BUCKET5_OVERALL

Ethereum - Tornado Cash Addresses