select

'Buy' as type,

block_timestamp,

tx_hash,

SENDER as trader,

AMOUNT_OUT_USD as usd_vol

from ethereum.core.ez_dex_swaps

where SYMBOL_OUT = 'WETH'

and date(block_timestamp) >= '2022-11-01'

UNION ALL

select

'Sell' as type,

block_timestamp,

tx_hash,

SENDER as trader,

AMOUNT_IN_USD as usd_vol

from ethereum.core.ez_dex_swaps

where SYMBOL_IN = 'WETH'

and date(block_timestamp) >= '2022-11-01'

SELECT date(block_timestamp) as date, type,

count(distinct tx_hash) as swaps,

count(distinct trader) as wallets,

sum(usd_vol) as usd_vol,

avg(usd_vol) as avg_usd_vol

from swap_data

group by date, type