SELECT

origin_from_address

, sum(CASE WHEN symbol_in = 'WETH' THEN 1 else 0 end) AS buys

, sum(CASE WHEN symbol_in = 'WETH' AND block_timestamp < '{{cutoffDate}}' THEN 1 else 0 end) AS buys_before

, sum(CASE WHEN symbol_in = 'WETH' AND block_timestamp >= '{{cutoffDate}}' THEN 1 else 0 end) AS buys_after

, sum(CASE WHEN symbol_out = 'WETH' THEN 1 else 0 end) AS sells

, sum(CASE WHEN symbol_out = 'WETH' AND block_timestamp < '{{cutoffDate}}' THEN 1 else 0 end) AS sells_before

, sum(CASE WHEN symbol_out = 'WETH' AND block_timestamp >= '{{cutoffDate}}' THEN 1 else 0 end) AS sells_after

, sum(CASE WHEN symbol_in = 'WETH' THEN amount_in else 0 end) AS weth_spent

, sum(CASE WHEN symbol_in = 'WETH' THEN amount_in_usd else 0 end) AS usd_spent

, sum(CASE WHEN symbol_out = 'WETH' THEN amount_out else 0 end) AS weth_received

, sum(CASE WHEN symbol_out = 'WETH' THEN amount_out_usd else 0 end) AS usd_received

, weth_received - weth_spent AS weth_pl

, usd_received - usd_spent AS usd_pl

, weth_spent + weth_received AS eth_volume

-- FROM ethereum.core.ez_dex_swaps

FROM ethereum.core.ez_dex_swaps

WHERE block_timestamp BETWEEN '{{startDate}}' AND '{{endDate}}'

AND (

-- buys -> where TOKEN_IN is the token and TOKEN_OUT = WETH

(token_in = lower('{{tokenAddress}}') AND token_out = lower('{{weth_address}}'))

OR

-- sells -> where TOKEN_IN = WETH and TOKEN_OUT is the token

(token_out = lower('{{tokenAddress}}') AND token_in = lower('{{weth_address}}'))

)

GROUP BY origin_from_address

HAVING eth_volume > 0.01

ORDER BY eth_volume DESC

),

txTotals AS (

SELECT

from_address

, count(1) AS txs

FROM ethereum.core.fact_transactions

WHERE from_address IN (SELECT DISTINCT origin_from_address FROM swaps)