with labels as ( SELECT * from flipside_prod_db.crosschain.address_labels WHERE 1=1 AND project_name ilike '%tornado%' ), base as ( SELECT block_timestamp, tx_hash, CASE WHEN event_name='Deposit' THEN ORIGIN_FROM_ADDRESS ELSE event_inputs:"to" END as actor, CASE WHEN event_name='Withdrawal' THEN coalesce(event_inputs:relayer, NULL) ELSE NULL END as relayer, CASE WHEN relayer IS NOT NULL THEN event_inputs:fee ELSE 0 END as fee, ADDRESS_NAME, TO_NUMERIC(regexp_substr(ADDRESS_NAME, '.*\\: (\\d+[\\.\\d]*)', 1, 1, 'e', 1), 10, 1) as token_amount, regexp_substr(ADDRESS_NAME, '.*\\: \\d+[\\.\\d]* (\\w+)', 1, 1, 'e', 1) as token, contract_address, ORIGIN_FUNCTION_SIGNATURE, event_name from ethereum.core.fact_event_logs f LEFT JOIN labels l on f.contract_address = l.address WHERE 1=1 AND block_timestamp BETWEEN CURRENT_DATE -(30*{{Months_Ago}}) AND CURRENT_DATE-1 AND ORIGIN_FUNCTION_SIGNATURE IN ('0x13d98d13', '0xb438689f') AND event_name IN ('Deposit', 'Withdrawal') ), refine as ( SELECT COUNT(DISTINCT tx_hash) as total_txns, SUM(token_amount) as token_amounts, concat(token,':', token_amount) as cat, date_trunc('day', block_timestamp) as ddate, actor from base WHERE event_name IN ('Deposit') GROUP BY actor,cat, ddate ), pre as ( SELECT r.actor, ddate, cat, total_txns, token_amounts, price, token_amounts*price as volume_usd from refine r LEFT JOIN ( SELECT avg(price) as price, CASE WHEN lower(SYMBOL)='weth' THEN 'eth' ELSE lower(SYMBOL) END as symbol, date_trunc('day', HOUR) as pdate FROM ethereum.core.fact_hourly_token_prices WHERE lower(symbol) IN (SELECT DISTINCT CASE WHEN lower(split(cat,':')[0])='eth' THEN 'weth' ELSE split(cat,':')[0] END as cat from refine WHERE cat IS NOT NULL) GROUP BY pdate,symbol ) p ON lower((split(r.cat,':')[0]))=p.symbol AND r.ddate =p.pdate WHERE 1=1 AND r.cat ilike '%{{ASSET}}%' ), pre_final as ( SELECT SUM(volume_usd) as volume_usd, actor from pre GROUP BY actor ) --THANKS TO MARQU for this hack lol SELECT COUNT(DISTINCT actor) as total_users, CASE when volume_usd < 10 then (floor(volume_usd))::string when volume_usd < 100 then (floor(volume_usd/10.0)*10)::string when volume_usd < 1000 then (floor(volume_usd/100.0)*100)::string when volume_usd < 10000 then (floor(volume_usd/1000.0)*1000)::string when volume_usd < 100000 then (floor(volume_usd/10000.0)*10000)::string when volume_usd < 1000000 then (floor(volume_usd/100000.0)*100000)::string when volume_usd < 10000000 then (floor(volume_usd/1000000.0)*1000000)::string when volume_usd < 100000000 then (floor(volume_usd/10000000.0)*10000000)::string when volume_usd < 1000000000 then (floor(volume_usd/10000000.0)*100000000)::string when volume_usd < 10000000000 then (floor(volume_usd/100000000.0)*1000000000)::string else volume_usd::string end as cat from pre_final GROUP by cat

[TC] Tornado Cash Addresses