select date_trunc('day', hour) as pdate, avg(price) as eth_price

from ethereum.core.fact_hourly_token_prices

where SYMBOL = 'WETH'

and pdate >= '2022-11-01'

group by pdate

),

swap_data as (

select

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'

and AMOUNT_OUT_USD > 0

UNION ALL

select

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'

and AMOUNT_IN_USD > 0

),

swap_agg as (

SELECT date(block_timestamp) as date,

count(distinct tx_hash) as swaps,

count(distinct trader) as wallets,

sum(usd_vol) as usd_vol,

avg(usd_vol) as avg_usd_vol