select distinct miner from ethereum.core.fact_blocks

where BLOCK_TIMESTAMP>='2022-01-01'

),

price as (

select

date_trunc(day,hour) as days,

avg(PRICE) as price from

ethereum.core.fact_hourly_token_prices

where symbol ilike 'weth' group by 1

select

date_trunc(day,block_timestamp)::date as days,

case when block_timestamp >= '2022-09-15' and block_number >= 15537394 then 'post merge' else 'pre merge' end as type,

count(distinct ORIGIN_FROM_ADDRESS) as tot_swappers,

price,

count(distinct TX_HASH) as tot_swaps,

sum(amount_in_usd) as swap_volume,

avg(amount_in_usd) as avg_swap_volume,

tot_swaps/tot_swappers as swaps_per_swapper

from ethereum.core.ez_dex_swaps s, price p

where EVENT_name = 'Swap' and p.days = s.block_timestamp::date

and block_timestamp between '2022-08-15' and CURRENT_date - 1

and symbol_out = 'WETH'

and amount_out_usd > 0 and amount_out_usd is not null

and ORIGIN_FROM_ADDRESS in (select miner from base )

group by 1,2,price