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 < '2023-02-03 21:55:00' THEN 1 else 0 end) AS buys_before

, sum(CASE WHEN symbol_in = 'WETH' AND block_timestamp >= '2023-02-03 21:55:00' 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 < '2023-02-03 21:55:00' THEN 1 else 0 end) AS sells_before

, sum(CASE WHEN symbol_out = 'WETH' AND block_timestamp >= '2023-02-03 21:55:00' 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

WHERE block_timestamp BETWEEN '2023-02-02 00:00:00' AND '2023-02-05 00:00:00'

AND (

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

(token_in = lower('0xe41de38463d2828f725d9d039d723279d2f37620') AND token_out = lower('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'))

OR

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

(token_out = lower('0xe41de38463d2828f725d9d039d723279d2f37620') AND token_in = lower('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'))

)

GROUP BY origin_from_address

HAVING eth_volume > 1

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)

GROUP BY from_address