select

BLOCK_TIMESTAMP::date as date,

count(distinct TX_HASH) as "# Buy TXs",

sum(AMOUNT_IN_USD) as "‌Buy Volume",

count(distinct ORIGIN_FROM_ADDRESS) as "# Buyer"

from ethereum.core.ez_dex_swaps

where BLOCK_TIMESTAMP between '2023-01-01' and '2023-01-16'

and EVENT_NAME = 'Swap'

and SYMBOL_OUT in ('WETH', 'ETH')

and symbol_in in ('USDC', 'USDT', 'DAI')

and symbol_in is not null and symbol_out is not NULL

and AMOUNT_IN_USD > 0

group by 1

),

swap_from_ETH as (

select

BLOCK_TIMESTAMP::date as date,

count(distinct TX_HASH) as "# Sell TXs",

-1 * sum(AMOUNT_IN_USD) as "Sell Volume",

count(distinct ORIGIN_FROM_ADDRESS) as "# Seller"

from ethereum.core.ez_dex_swaps

where BLOCK_TIMESTAMP between '2023-01-01' and '2023-01-16'

and EVENT_NAME = 'Swap'

and SYMBOL_IN in ('WETH', 'ETH')

and symbol_out in ('USDC', 'USDT', 'DAI')

and symbol_in is not null and symbol_out is not NULL

and AMOUNT_IN_USD > 0

group by 1

),

price as (

select

hour::date as date,

avg(price) as ETH_Price

from ethereum.core.fact_hourly_token_prices

where hour between '2023-01-01' and '2023-01-16'